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 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.
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.])) )Hi v-piga-msft just to clarify I said the cross filter was the easiest. But my attachment compared several options and recommended forcing the context via DAX or if possible slice off a bridge table that forced a standard relationship down into each of the tables. One question I have is the performance differecne of using CROSSFILTER vsreferencing the other table name (which I assume implies a cross filter) This is the method I currently favor but if there is a performance benefit to using crossfilter vs just the table name I will start using that.
Thanks parry2k and Seward12533!
BTW parry2k, do you know how to make the line start from week 1 as well?
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)Awesome! Thanks Seward12533!