Forum Discussion
Running Total Breaks when Adding Top N
- 5 years ago
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 ) - 5 years ago
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]))
Thanks Fowmy , good to know I'm not a complete dunce. It *does* work as long as I don't have Top N filter applied on [App Name] by [Total Monthly Cost]. Once I apply the filter however, it no longer calculates the running total.
- Fowmy5 years agoSuper User
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 )- Sburi25 years agoNew Member
That's so odd... I open up your file and it works. I open up my file and it doesn't. I compare the DAX formulas and they are functionally the same. I'm not sure where I'm going wrong.
I also just noticed that it actually is performing a cumulative count, but only when the previous numbers exactly equal the current numbers. I'm not using =, I'm using >=... not sure what's wrong.