Forum Discussion
Cumulative Sum wrong when using visual filter
Hello,
I have a strange behaviour (at least to me! :) ) of Power BI Desktop report, for cumulative sum measure.
Here is the measure:
NaplKumulativ =
VAR CurrNap = [NaplTotal]
RETURN
CALCULATE([NaplTotal];
FILTER(ALL(SellOut[Proizvođač]); [NaplTotal] >=CurrNap )
)And everything is fine if I don't have Visual filter.
But if I add visual filter (for instance, I want only those who contribute to the first 50% of total), some rows are not correct in cumulative sum, i.e. they are duplicated. Photo:
however, if I select all in Slicer, everything is ok again!
If I make multiple selection, but not all, wrong results appear again in some rows (but not necessarily the same ones like for a single selection).
So, to summarize:
- no visual filter, single selection in slicer - cumsum is OK
- visual filter, single selection in slicer - cumsum is NOT OK
- visual filter, select all in slicer - cumsum is again OK
What might cause such a behaviour?
Thank you.
Your ALL is overriding your slicer in the context of the calculation. That's what ALL does. If you want to preserve your filtering in your report, you need to use ALLEXCEPT and in the ALLEXCEPT arguments you need to specify your slicer column.
4 Replies
The issue is likely your ALL in your filter clause. You probably need to use ALLEXCEPT.
I am not sure how can I use ALLEXCEPT instead of ALL in this case?!
Just to add, it does not matter what value I set in filter. It could be 200% (literally all products), but the behaviour is the same like for 30% or 50% or 70%.
As long as filter is present, value is wrong for some rows.
Important thing may be that I don't have that column in dimension. It is in fact table. I may try to move it , to create dimension, although there are some issues to do so (dirty data, additional preparation).
Your ALL is overriding your slicer in the context of the calculation. That's what ALL does. If you want to preserve your filtering in your report, you need to use ALLEXCEPT and in the ALLEXCEPT arguments you need to specify your slicer column.
