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.

 

 

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! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





View solution in original post

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)

View solution in original post

Nicklancer Frequent Visitor
Frequent Visitor

Re: Visual Filter for Cumulative Sum Issue

Awesome! Thanks @Seward12533!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 430 members 4,625 guests
Please welcome our newest community members: