Forum Discussion
DAX Running Total (ASC) by category
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)
)
)
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
4 Replies
- ValtteriN
Community Champion
Hi,
For running totals I recommend using this pattern by SQLBI:Sales RT :=VAR MaxDate = MAX ( 'Date'[Date] ) -- Saves the last visible dateRETURNCALCULATE ([Sales Amount], -- Computes sales amount'Date'[Date] <= MaxDate, -- Where date is before the last visible dateALL ( 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/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])RETURNCALCULATE(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
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
- I added the pattern you recommended as 'To Go Amount 2', this works but only if there is 1 state like the first pattern.