Forum Discussion
Reverse Running Total Measure per Category/Bin (WINDOW Function) - Avoiding Multiple Tables
- 5 months ago
Thank you for sharing the model, I have checked and you would need to use Summarize to reshape the data to one row per Category & Utilization bin.
I have checked and this DAX measure would work on the model if you replace it in Table 1Reverse running total (%) = VAR BinTable = SUMMARIZE ( ALLSELECTED ( 'Table Sum Max 1' ), 'Table Sum Max 1'[Category], 'Table Sum Max 1'[Utilisation (%) (bins)], "_Count", COUNTROWS ( 'Table Sum Max 1' ) ) VAR CurrentBin = MAX ( 'Table Sum Max 1'[Utilisation (%) (bins)] ) VAR CurrentCategory = SELECTEDVALUE ( 'Table Sum Max 1'[Category] ) VAR TotalPerCategory = SUMX ( FILTER ( BinTable, [Category] = CurrentCategory ), [_Count] ) VAR ReverseCumulative = SUMX ( FILTER ( BinTable, [Category] = CurrentCategory && [Utilisation (%) (bins)] >= CurrentBin ), [_Count] ) RETURN DIVIDE ( ReverseCumulative, TotalPerCategory ) * 100If my answer helped you solve the problem, please consider accepting it as the solution and help other members to use the same solution in same/similar situations.
Hi,
Woul it be possible to share the download link of an MS Excel file with your formulas already written there for the reverse running total? I will try to translate those formulas into DAX measures.
The Power BI available in my link is all you need to start developing a solution.
The Excel file was generated with Alteryx to provide a random set of dates, categories, etc. and then Excel for the random numbers.
Edit:
Also, I cannot upload files on this forum as there is no capability provided for beginner accounts, making this even more cumbersome.