How does the AI/BI Dashboard Custom Calculations feature actually work?

[dougtrajano]

I am trying to compute the market_share measure using the Custom Calculations functionality in the AI/BI Dashboard.

My dataset looks like this:

• empresa (company): The name of the company.
• acessos (accesses): Count of access.

My Custom Calculation expression is this one:

It appears that the Custom Calculations feature applies to each row and cannot reference dataset statistics, such as the sum of a given column.

As far as I know, I should be able to do that in the Legacy Dashboard, but it was deprecated. It seems that AI/BI Dashboards are quite limited.

System Info

• Azure Databricks
• Serverless SQL Warehouse

[Shua42]

Hi @dougtrajano ,

The idea of the custom calculations here are that they evaluate to a single value for the entire table. In the expression here, you are trying to divide a column (acessos) by a single value (sum of acessos). The error is happening because this wouldn't return a single value for the table, it would evaluate to a single value for each row.

Based on your description, I think what you want here is either:

1. Another column that is the value in acessos / sum(acessos), which should be defined in the query.

2. A single value, in which case you'd need some sort of agregation on the numerator (currently the whole column 'acessos') to have it evaluate to a single value, so that the expression as a whole can return a single value.

If you have a clear description of what you are trying to calculate, I can help you modify the expression.

[dougtrajano]

Thank you for your response.

I want to calculate the market share. To do so, I must divide each row value by the sum of the "acessos" column.

I was able to do that in the query using an window function.

sum(acessos) / sum(sum(acessos)) OVER () AS market_share

However, including that in the query limits the type of filter I can use. I would like to use the "Field" filter type so the filters can dynamically reflect column values. If I add this expression to the query, I can only use the "String," "Numeric," and "Date" types. The "Query Based Dropdown List" option, which is available in the SQL Editor, is not accessible in the AI/BI Dashboard.

Description of what I am trying to calculate

I am creating a Dataset in the AI/BI Dashboard, this dataset returns two columns and applied several filters in a given UC table. The columns are:

• company: The name of the company.
• sum(views) AS total_views: The count of views.

I would like to calculate the market_share based on total_views column in a way that I can change the Dashboard Filters and it reflects to the market_share calculation.

[Shua42]

Hi @dougtrajano ,

I've been playing around with trying to recreate this on my end, and my understanding is that you're trying to have a filter selection dynamically effect a calculation. Unfortunately I don't think this type of behavior is currently supported, as custom calculations don't take filter's into their context. There might be way to hack this behavior i you get clever with the data setup, but nothing came to mind for me as I was trying it out.

[Alex_Lichen · Accepted solution]

Hi Doug,

Thanks for testing out the custom calcs feature! You're correct that today, we don't yet support "level of detail" type calculations, where you can specify an aggregation to ignore group bys. We are hoping to introduce this in coming quarters.

[dougtrajano]

Hi Alex, thank you for your response!

I will create a Databricks app for it as a workaround while we do not support it natively in custom calculations. Thank you!

Thank you, Shua42, for trying to help me.

[dougtrajano · Accepted solution]

Actually, I was able to do that using the AI/BI Dashboard.

I created query-based parameters and computed the market share in my own SQL Query.