Forum Discussion

MrLolTroll's avatar
MrLolTroll
Frequent Visitor
5 months ago
Solved

Reverse Running Total Measure per Category/Bin (WINDOW Function) - Avoiding Multiple Tables

I have multiple categories in my data set, where for each I need to calculate a reverse running total with a measure that I can then plot on a line clustered column chart as a line: This is th...
Need Help
  • garvitgupta96's avatar
    garvitgupta96
    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 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. 

    Thanks,
    Garvit Gupta
    Linkedin
garvitgupta96's avatar
garvitgupta96
Icon for Resolver II rankResolver II
5 months ago

Hi MrLolTroll,

 

The incorrect reverse running total occurs because when you use the full dataset with multiple categories, the WINDOW() function is evaluated across all categories combined, not per category. Your manual categorical tables worked because it implicitly partitioned the data and the Windows function only did the calculation. The right way would be using the Partitionby function as shown in the measure below:

Reverse running total (%) =
SUMX (
    WINDOW (
        1,
        ABS,
        0,
        REL,
        ALLSELECTED ( 'Table Sum Max 1' ),
        ORDERBY (
            'Table Sum Max 1'[Utilisation (%) (bins)],
            DESC
        ),
        PARTITIONBY (
            'Table Sum Max 1'[Category]
        )
    ),
    'Table Sum Max 1'[% grand total]
)

This would Partition the data based on each category and accumulate bins correctly when filtered on visual. 

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. 

Thanks,
Garvit Gupta
Linkedin
MrLolTroll's avatar
MrLolTroll
Frequent Visitor
5 months ago

Tried this but it returns the same result. I frogot to mention - PARTITIONBY doesn't work either.