PowerBI DAX: logic to use aggregated table as parameter in functions or another workaround to calculate dataset KPI filtered by any field?

0
votes

In PowerBI, I need to create a Performance Indicator (KPI) measure which evaluates dataset values in a scale from 0 to 1, with target (1) being the MAX value in a 20 years history. It's a national airport trip records open database. The formula is basically [value]/[max value].

My dataset has a lot of fields and I wish I could filter it by any of these fields, with a line chart showing the 0-1 indicator for each month based on the filters.

This is my workaround test solution:

Table 1 - Original dataset: if I filter something here, below tables also update (there are more fields to the left, including YEAR and MONTH

Table 1 - Original dataset: if I filter something here, below tables also update (there are more fields to the left, including YEAR and MONTH

Table 2 - Reference to original dataset, aggregating YEAR-MONTH by the sum of "take-offs" (decolagens)

Table 2 - Reference to original dataset, aggregating YEAR-MONTH by the sum of "take-offs" (decolagens)

Table 3 - Reference to above (sum) table, aggregating MONTH by the max of "take-offs" (decolagens)

Table 3 - Reference to above (sum) table, aggregating MONTH by the max of "take-offs" (decolagens)

Table 4 - 'Sum table' merged to 'Max table' by MONTH as new table: then do [Value]/[Max] and we've got the indicator

Table 4 - 'Sum table' merged to 'Max table' by MONTH as new table: then do [Value]/[Max] and we've got the indicator So if i filter the original dataset by any fields, all other tables update accordingly and the indicators always stays between 0-1, works like a charm.

TL;DR

The problem is: I need to create a dashboard of this on Power Bi. So I need this calculation to be in a measure or another workaround.

My possible solution: by pure DAX code in the measure field, to produce Tables 2 and 3 so I'll divide the month sum values by their month max value (which will both be produced according to PowerBi dashboard slicers) and get the indicator dinamically produced.

I'm stuck at: I don't understand how can I reference a sum/max aggregate table in dax code. Something like = SUM (dataset[take-offs]) / MAX (SUM (dataset[take-offs])). Of course these functions do not work like that, but I hope I made my point clear: how can I produce this four table effect with a single measure?

Other solutions are welcome.

Link to the original dataset: https://www.anac.gov.br/assuntos/dados-e-estatisticas/dados-estatisticos/arquivos/DadosEstatsticos.csv

It's an open dataset, so I guess there's no problem sharing it. Please help! :)

EDIT: please download the dataset and try to solve this. Personally I think it's a quality statistics doubt that will eventually help others. The calculation works, it only needs a Power Bi Measure port.

1
excelstatisticspowerbidata-sciencedax

1 Answers

0
votes

Add the ALL formula:

Measure = SUMX(ALL('Table'),[Valor])/SUM('Table'[Max])

Example