Measures using ALLEXCEPT and slicers

[yharfush]

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:

State	Vehicle	Socioeconomic level	Gender	Age Group	Penetration	Population
NY	INTERNET	A/B/C+	Men	13 a 18	180	194
NY	INTERNET	A/B/C+	Men	19 a 24	217	222
NY	INTERNET	DE	Men	25 a 34	309	319
NY	INTERNET	DE	Women	35 a 44	281	285
NY	INTERNET	DE	Women	45 a 64	318	334
NY	RADIO	DE	Men	13 a 18	195	195
NY	RADIO	A/B/C+	Men	19 a 24	224	225
NY	RADIO	A/B/C+	Men	25 a 34	336	339
NY	RADIO	A/B/C+	Women	35 a 44	312	316
NY	RADIO	DE	Women	45 a 64	304	384

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!

[Greg_Deckler · Accepted solution]

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

[yharfush]

Ah yes this works! Thank you :)!!