Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Visual Filter for Cumulative Sum Issue

Hi,   I need some help with cumulative sum line.   Before selecting any slicer, the visual shows this: But after applying filter, the visual shows the below. How then can I make the...
Need Help
  • parry2k's avatar
    parry2k
    8 years ago

    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.]))
)
  • Seward12533's avatar
    Seward12533
    8 years ago

    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)
Anonymous's avatar
Anonymous
Not applicable
8 years ago

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