Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mmainza
Frequent Visitor

Measure to dynamically compare totals from two different filters; ignore a specific filter

Really struggling with this one. I'm trying to do a comparison of charitable giving in two different geographic regions. User can choose the two regions to compare with two different slicers. This is easy when the same metrics are in standalone visuals because I can just use the Edit Interactions options.

 

However, I also want to display these metrics in the same visual (a bar chart) beside one another, which means I need to create two measures.

 

Here's what I have tried so far.

 

Two columns for filtering in slicers - Chosen Region and Comparison Region

 

ChosenRegionTotal = CALCULATE ( SUM ( 'Transactions'[Total Spent] ), ALLEXCEPT ('Accounts','Accounts'[Chosen Region]) )

 

When I place this in a card, it only works if I turn off the interaction from the Comparison Region filter. This is not an option because I want to display this in a bar chart alongside the ComparisonRegionTotal measure (same thing but ignoring the Chosen Region column filter). If it's an ALLEXCEPT, shouldn't ignore the other column's filtering?

 

I'm hoping it's because I'm just fundamentally confused about ALL and ALLEXCEPT, but I feel like there has to be a solution.

 

Data is sensitive so I can't supply a report.

Capture.JPG

 

 

 

1 ACCEPTED SOLUTION

Seems you're correct, although I still don't understand why ALL or ALLEXCEPT won't work in this scenario.

 

I was able to get the result I wanted, but only by duplicating the table.

View solution in original post

2 REPLIES 2
v-sihou-msft
Employee
Employee

@mmainza

 

No, it's not possible. When you put both measures into same chart visual, they must take all slicers selection in calculation. We can't have one measure take one slicer, and make other measure take another slicer. 

 

Regards,

Seems you're correct, although I still don't understand why ALL or ALLEXCEPT won't work in this scenario.

 

I was able to get the result I wanted, but only by duplicating the table.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.