custom calculation of percentage of total and cumulative percentage

[genebaldorios]

Is there a way to calculate the percentage of total using the Databricks dashboard's custom calculations and also the cumulative percentage? What function would be equivalent to the DAX All and AllSelect functions, but using Databricks' aggregation functions through the custom calculation editor? I'm using the custom calculations because using a SQL query would lose the granularity of the data, which would cause errors in the visuals. For example: Sum(value)/sum(value) over()

[szymon_dybczak · Accepted solution]

Hi @genebaldorios ,

I don't use databricks dashboards on my project (we are PBI shop), but I guess you need to use AGGREGATE OVER clause with cumulative frame:

[genebaldorios]

Thanks for the tip, the measurements are as follows:

1. Percentage of Total:

SUM(qty) / (SUM(qty) AGGREGATE OVER (ORDER BY status DESC ALL))

2. Cumulative Percentage SUM(qty) / (SUM(qty) AGGREGATE OVER (ORDER BY status DESC Cumulative))

Calculates the percentage of the cumulative total, sorted by status.