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.

    • garvitgupta96's avatar
      garvitgupta96
      Icon for Resolver II rankResolver II
      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
      • MrLolTroll's avatar
        MrLolTroll
        Frequent Visitor
        5 months ago

        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.