Power BI DAX tips: using CALCULATE and ALL

In education data, we often need to compare one percentage with another. For example the attendance of our whole school with the attendance of a particular class or year group. Or the P8 of one group of pupils, compared to the P8 of the whole school.

It took me a few attempts before I managed to get these DAX measures right – and the secret is to use the CALCULATE function. So here’s how to use DAX to create measures that will help you solve these types of scenarios. Watch the video and see the notes below:

Here are the three DAX measures described in the video:

Attendance % Measure = SUM('Attendance Summary'[Present + AEA])/SUM('Attendance Summary'[Possible])

Attendance % all year groups = 
CALCULATE (
    'Attendance Summary'[Attendance % Measure],
    ALL ( 'Pupil Data Extended'[Year taught in Code] )
)

Attendance % R-Y6 = 
CALCULATE (
    'Attendance Summary'[Attendance % Measure],
    FILTER (ALL('Pupil Data Extended'),'Pupil Data Extended'[Year Index] >= 0)
)

Of course, your tables and column names will be different, but hopefully these tips will help. Any questions or suggestions for improvements are always welcome.