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...
  • 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