Forum Discussion

MrLolTroll's avatar
MrLolTroll
Frequent Visitor
4 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 the correct and expected result, however, I had to create a separate table with filter for Category A:

Table Sum Max 2 =
SUMMARIZE(FILTER('Table Sum', 'Table Sum'[Category]="A"), ...)

The measure for 'Table Sum Max 2' looks like this:

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


If I take the entire data set in 'Table Sum Max 1' with all the categories, the measure needs to be changed to:

Reverse running total (%) 1 =
CALCULATE(SUM('Table Sum Max 1'[% grand total]), WINDOW(1,
ABS,
0 ,
REL,
FILTER(ALLSELECTED('Table Sum Max 1'[Utilisation (%) (bins)]),DISTINCT('Table Sum Max 1'[Utilisation (%) (bins)])),
ORDERBY('Table Sum Max 1'[Utilisation (%) (bins)],
DESC)
))

The calculated result does not match the 'Utilisation (%) (bins)' column unique values - it sums all of them per 'Utilisation (%)' column:

I tried adding "context" to this measure by using:

FILTER(ALLSELECTED('Table Sum Max 1'[Utilisation (%) (bins)]),DISTINCT('Table Sum Max 1'[Utilisation (%) (bins)]))

I also tried adding GROUPBY or DISTINCT to CALCULATE - results are incorrect.

 

How to provide that context to the 'Reverse running total (%) 1' measure so it returns correct reverse running total values per bin when filtered on a visual?

 

Circumnavigating that by creating multiple tables filtered based on category requires adding necessary columns and measures to each of them which is tedious. I'd expect it should be able to detect these categories dynamically.

 

Here is the link to the .pbix file:

https://drive.google.com/file/d/1VtGo9fC_8XEP2Q2vf8p5P1ByiryGKKru/view?usp=drivesdk

Need Help
  • garvitgupta96's avatar
    garvitgupta96
    4 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

8 Replies

  • garvitgupta96's avatar
    garvitgupta96
    Resolver II
    4 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
      4 months ago

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

      • garvitgupta96's avatar
        garvitgupta96
        Resolver II
        4 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
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 months ago

    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. 

    • MrLolTroll's avatar
      MrLolTroll
      Frequent Visitor
      4 months ago

      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.