Forum Discussion
Visual Filter for Cumulative Sum Issue
AnonymousUpdate cummulative total DAX as give below and that will do it, as Seward12533 suggested to use cross filter to both direction, it has performance hit but instead of setting up the relationship, you can use it in DAX formula basically you are using cross filter to both direction when required
Cumulative = CALCULATE( SUM('Table2'[Count]), CROSSFILTER(Table1[Week of Year],Table2[Week No.], Both), FILTER(ALL('Table2'), 'Table2'[Week No.]<=MAX('Table2'[Week No.])) )Anonymous try this
Cumulative = VAR Result = CALCULATE( SUM('Table2'[Count]), CROSSFILTER(Table1[Week of Year],Table2[Week No.], Both), FILTER(ALL('Table2'), 'Table2'[Week No.]<=MAX('Table2'[Week No.])) ) RETURN IF(Result,Result,0)
Hi Anonymous,
It seems that you want to have a visual filter with cumulative Sum?
Could you share a screenshot of your visual filter setting?
Actually, if you have a visual filter for cumulative sum, it will show the values match the filter condition.
Best Regards,
Cherry
Hi v-piga-msft,
I used a donut chart based on the category. So for example, when no category is applied, it shows this:
When I choose a category, say for category A:
Now obviously this looks fine since this constitutes most of the values, however the cumulative line is still not right, as it still shows the total, not total by category.
This becomes ugly when I apply to Category C:
My slicer looks like this:
Hope my explanation is clear enough.
Regards,
Nicholas Hiew
Here is the pbix file if anyone is interested:
https://drive.google.com/file/d/1pT9GoD_vD0DZfVpwDKHkzDwuuU63nnEB/view?usp=sharing
Hi Anonymous,
Do you want to get the output like below?
When I click "B" on the Donut chart, it will show this.
When I click "D" on the Donut chart, it will show like below.
If you want the output above, you could have a reference of the attachment.
Best Regards,
Cherry
Hi v-piga-msft,
I would like the cumulative line to be dynamic as well. Meaning when I choose category A, the cumulative line changes to Category A's cumulative line.
Hi, it has to do with the filter context not applying the way you think it will. When you use the slicer on one table it does not force that relationship backwards against the arrows. There are may ways to handle it. The simlest is to edit the relationship so it crossfilters in both directions. This however is not the most robust solution. Take a look at this tutorial I put together for an internal user group for some more robust solutions that will work as you develop more complicated models.