DAX Running Total (ASC) by category

[Anonymous]

Hi,

I have my data broken up into 100 groups and I want to know how much of an amount is left from the current row to the last row (100). The formula works well when I only have one state in the visual. If I add multiple states it breaks down. 

I have tried a couple of methods at this point, one uses IsOnOrAfter and the other just a filter between current row and row 100. I figure I am missing some kind of command with the [StoreState] Field but I am unsure how to proceed. Can someone help me calculate this running total by category?

To Go Amount= 

CALCULATE(

    SUM('Table'[Amount]),

    FILTER(

    ALLSELECTED('Table'[StatePercentile]),

    ISONORAFTER('Table'[StatePercentile], MIN('Table'[StatePercentile]), asc)

    )

)

[ValtteriN]

Hi,

For running totals I recommend using this pattern by SQLBI: 

Sales RT :=

VAR MaxDate = MAX ( 'Date'[Date] ) -- Saves the last visible date

RETURN

    CALCULATE (

        [Sales Amount],           -- Computes sales amount

        'Date'[Date] <= MaxDate,  -- Where date is before the last visible date

        ALL ( Date )              -- Removes any other filters from Date

    )
Also check their post about the topic: https://www.sqlbi.com/articles/computing-running-totals-in-dax/

If you want to change your calculation logic by hierarchy level in matrix one method is to use ISFILTERED. e.g. IF(ISFILTERED(Table[StoreState]),0, 1)

The problem that likely is occurring in your calculation is that now the asc is applied at total level. So you could consider adding all([StoreState]) to you calculate.

Ping me with @ if these tips don't help you to solve the issue.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[Anonymous]

@ValtteriN Thanks for the quick reply. 

I read through the tips and the article but no luck so far. 

• I added the pattern you recommended as 'To Go Amount 2', this works but only if there is 1 state like the first pattern. 
  
  To Go Amount 2 = 

  VAR CurrentStatePercentile = SELECTEDVALUE('Table'[StatePercentile])

  RETURN

      CALCULATE(

          SUM('Table'[Amount]),

          'Table'[StatePercentile] >= CurrentStatePercentile

         -- ALL ('Table'[StoreState])

      )
• I also tried adding the ALL([StoreState]) to both patterns. This applies a running total to the entire table across states. I need a Running total within each state.
• Lastly, I do not understand how to apply the ISFILTERED
  
  Thank you!
  
  

[ValtteriN · Accepted solution]

Hi @Anonymous ,

Try something like this:

RT_Keep Dim = CALCULATE(SUM(RT_dimensions[Value]),ALLEXCEPT(RT_dimensions,RT_dimensions[Dim1]),RT_dimensions[Dim2]<=MAX(RT_dimensions[Dim2]))

Example:
ALLEXCEPT keeps the filter on dim1

[Anonymous]

@ValtteriN Thank you, yes, ALLEXCEPT got me to the right place.