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.
Tried this but it returns the same result. I frogot to mention - PARTITIONBY doesn't work either.
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 1
Reverse 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 ) * 100
If 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.
- MrLolTroll5 months agoFrequent Visitor
Thank you very much, this makes sense given how data are aggregated.
It does make me wonder though, what's the point of PARTITIONBY in the WINDOW function if it works the same way as GROUPBY, DISTINCT, and FILTER?
Isn't that a duplication of functionality rather than a parameter for giving context?
It serves no purpose in it's current state if it yields the same result as without it.
- garvitgupta965 months ago
Resolver II
PARTITIONBY did not work in your scenario because it only tells the WINDOW() function where to reset the running calculation, but it does not change the granularity at which the measure is evaluated which in your case there are multiple physical rows per Utilisation (%) bin, so the window operation was still being executed at row level rather than at bin level. As a result, even though PARTITIONBY correctly separated categories, each partition still contained duplicated bin values, and Power BI subsequently aggregated those multiple row‑level results when rendering the visual, producing distorted, non‑monotonic curves. PARTITIONBY cannot collapse or deduplicate rows and therefore cannot fix this class of problem. Using Summarize, we tweaked the data to a distinct row table due to which the running calculation was able to pick the right granularity.
I hope this clarifies your doubt.
- MrLolTroll5 months agoFrequent Visitor
Thanks, I appreciate the help.