cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
yharfush
Frequent Visitor

Measures using ALLEXCEPT and slicers

Hello,

 

I am having trouble using an ALLEXCEPT measure that takes into account the slicers I have placed on my report.

 

My table has different Vehicle penetration by state, the table contains the Socioeconomic level-Gender-Age Group detail:

 

StateVehicleSocioeconomic levelGenderAge GroupPenetrationPopulation
NYINTERNETA/B/C+Men13 a 18180194
NYINTERNETA/B/C+Men19 a 24217222
NYINTERNETDEMen25 a 34309319
NYINTERNETDEWomen35 a 44281285
NYINTERNETDEWomen45 a 64318334
NYRADIODEMen13 a 18195195
NYRADIOA/B/C+Men19 a 24224225
NYRADIOA/B/C+Men25 a 34336339
NYRADIOA/B/C+Women35 a 44312316
NYRADIODEWomen45 a 64304384

 

 

In my report I have 4 slicers (one for Socioeconomic level, one for Gender, one for Age Group and one for Vehicle). I have also created a Shape map on which states get a different color depending on whether the % of penetration of a vehicle in that state is higher (green) or lower (red) than the national % penetration for that same vehicle.

 

So I created two measures:

 

% Penetration = sum('MyTable'[Penetration]) / sum('MyTable'[Population])
National % Penetration = CALCULATE([% Penetration], ALLEXCEPT('MyTable', 'MyTable'[Vehicle]))
 
This is working perfectly when I have not applied any slicers on Gender, Socioeconomic level or Age Group. However, when I select any of these slicers, the National % Penetration doesn't change to adjust to that selection, so the colors displayed on the map are not correct. The % Penetration variable is taking into account the slicer selection but the National % Penetration is not.
 
How can I fix this?
Any help would be greatly appreciated!
1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: Measures using ALLEXCEPT and slicers

That's because your ALLEXCEPT removes all other filters except the ones that you specify. Try adding your other columns into your ALLEXCEPT function.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

2 REPLIES 2
Super User IV
Super User IV

Re: Measures using ALLEXCEPT and slicers

That's because your ALLEXCEPT removes all other filters except the ones that you specify. Try adding your other columns into your ALLEXCEPT function.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

yharfush
Frequent Visitor

Re: Measures using ALLEXCEPT and slicers

Ah yes this works! Thank you :)!!

Helpful resources

Announcements
June 2020 Community Challenge: Can You Solve These?

June 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors