Forum Discussion

yharfush's avatar
yharfush
Frequent Visitor
6 years ago
Solved

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!
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

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

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

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