cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Community Support Team
Community Support Team

Re: Visual Filter for Cumulative Sum Issue

Hi @Nicklancer,

 

Do you want to get the output like below?

 

When I click "B" on the Donut chart, it will show this.

 

 

B.PNG

 

When I click "D" on the Donut chart, it will show like below.

 

 

D.PNG

 

If you want the output above, you could have a reference of the attachment.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Nicklancer Frequent Visitor
Frequent Visitor

Re: Visual Filter for Cumulative Sum Issue

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.

 

 

Highlighted
Seward12533 New Contributor
New Contributor

Re: Visual Filter for Cumulative Sum Issue

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.

 

https://1drv.ms/u/s!AuCIkLeqFmlhhJgzLZ6jPcWgLGu3Yw

Super User
Super User

Re: Visual Filter for Cumulative Sum Issue

@NicklancerUpdate 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.]))
)

 




Did I answer your question? Mark my post as a solution!


Proud to be a Datanaut! Connect with me on Linkedin.
Feel free to email me with any of your BI needs.




 




Seward12533 New Contributor
New Contributor

Re: Visual Filter for Cumulative Sum Issue

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.

Nicklancer Frequent Visitor
Frequent Visitor

Re: Visual Filter for Cumulative Sum Issue

Thanks @parry2k and @Seward12533!

 

BTW @parry2k, do you know how to make the line start from week 1 as well?

Seward12533 New Contributor
New Contributor

Re: Visual Filter for Cumulative Sum Issue

@Nicklancer 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)
Nicklancer Frequent Visitor
Frequent Visitor

Re: Visual Filter for Cumulative Sum Issue

Awesome! Thanks @Seward12533!