Visuals and the wonderful mysteries of the CALCULATE function

I haven't really talked about visuals much as they're mostly pretty intuitive in BI but there is an issue if you want to produce a graph like this as the obvious steps won't get you there.

So that's a bar chart showing the percentage of kids of each gender at low, middle high prior attainment from this Students table.

But if you create the bar chart with Gender as the Axis, Prior Attainment as the Legend and Count of student ID as the value you get this:

If you click the down arrow next to count of studentid you can use 'show value as....percent of grand total' 

but that gives you:

(note figures were added by going to the format (roller paint icon of the visual) and turning data labels on)

The problem here is that the totals for each segment are being divided by all pupils not by Male/Female totals respectively.

Row and Filter Context

We can sort this out, but to understand how we need to understand two very important concepts in power bi, row context and filter context.

Row context is the combined effect of filters that are applied to a figure by its place in a visual or table. So the row context of the figure 30.09 above is gender = F and prior attainment = low.

Filter context is the combined effect of additional filters acting on the report/page/visual. Filter context can be fed by slicers, selection of other visual elements, filters applied direvtly to the visual/page/report as well as from within expressions.

The calculation powering our visual is currently this:

Students where Gender = X and PA  = Y / All(Students)

We want to change it to this:

Students where Gender = X and PA  = Y / Students where Gender = X 

CALCULATE Function

To do that we're going to use the CALCULATE function. Calculate has the syntax 
CALCULATE (expression, filter)

What it does is allow you to replace the current row and filter context, essentially stripping out what BI does automatically so it can be built back in the way you want.

So if we create a new measure:

TruePc = COUNTROWS (students) / CALCULATE(COUNTROWS (students),ALL(students))

And use it as the value of the visual then the visual will be unchanged. The numerator ((COUNTROWS(Students) )is the number of total students with row and filter context applied. The denominator (CALCULATE...) is the number of students with those contexts removed - ie all rows of table students. 

What we want now is to put back in some (but not all) of those contexts to the denominator and we can do that with the ALLEXCEPT function. ALLEXCEPT has the syntax (<table>,<column1>,<column2>...) and preserves any filtering from row or filter context applied to its named columns.

So our measure becomes:

TruePc = COUNTROWS (students) / CALCULATE(COUNTROWS (students),ALLEXCEPT(Students,Students[Gender]))

And hey presto, our visual now shows:



Adding Slicers

Once you've used ALLEXCEPT in a measure you need to be careful when adding additional filters/slicers because your ALLEXCEPT function means all rows from the students table except those affected by named filters. So if you add a pupil premium slicer for example then the percentages will suddenly be off because the COUNTROWS(Students) numerator of your measure will respect the filter but the denominator won't until you add PupilPremium to the ALLEXCEPT function to tell it to respect filters applied to that column.