Forum Discussion
Running Total Breaks when Adding Top N
Hello!
I'm trying to obtain obtain a running total. I have the DAX working:
Running Total =
VAR thisCategory = [sum Total Monthly Cost]
VAR RunningTotal_ThisCategory =
CALCULATE(
[sum Total Monthly Cost],
FILTER(ALLSELECTED(tbApptioAppCosts[App Name]), [sum Total Monthly Cost]>=thisCategory)
)
RETURN
RunningTotal_ThisCategory
However, when I filter the visual to Top 10 by App Name, the running totals fails to be cumulative.
Would really appreciate help understanding what is happening under-the-hood so I can resolve issues like this in the future myself.
This is what I want
| Correct Expectation | Total | Cumulative Total |
| Cat A | 300 | 300 |
| Cat B | 200 | 500 |
| Cat C | 100 | 600 |
This is what I'm getting
| What's Happening | Total | Cumulative Total |
| Cat A | 300 | 300 |
| Cat B | 200 | 200 |
| Cat C | 100 | 100 |
Thank you!
Sburi2
I made a sample file. as you can see, I filtered by top 3, and still, the result is correct. In your initial posting of the question, you had used ALLSELECTED when you calculated the all appname total, you should use ALL. check my formula below: I attached the file as well.Cummulative = var __current = [Total Sales] var __all = CALCULATE( [Total Sales] , ALLSELECTED(financials[Product])) var __cumm = CALCULATE( [Total Sales], FILTER( ALLSELECTED(financials[Product]), [Total Sales] >= __current ) ) return DIVIDE( __cumm , __all )Sburi2
When you posted the question initially, I noticed that you had used ALL instead of ALLSELECTED. It was one reason for the incorrect results when you applied TOPn filter. Apart from that you also had applied some other filters.ar __all = CALCULATE( [Total Sales] , ALLSELECTED(financials[Product]))
9 Replies
Sburi2
I made a sample file. as you can see, I filtered by top 3, and still, the result is correct. In your initial posting of the question, you had used ALLSELECTED when you calculated the all appname total, you should use ALL. check my formula below: I attached the file as well.Cummulative = var __current = [Total Sales] var __all = CALCULATE( [Total Sales] , ALLSELECTED(financials[Product])) var __cumm = CALCULATE( [Total Sales], FILTER( ALLSELECTED(financials[Product]), [Total Sales] >= __current ) ) return DIVIDE( __cumm , __all )
