Calculate a single measure using values with different filters

[Anonymous]

Hi,

I'm trying to do something which I think should be relatively simple but I'm struggling to do it Power BI.

I have data covering students taking various undergraduate study courses. The data also contain columns with details of subsequent postgraduates courses if the student continues their studies, which are blank otherwise.

I'm trying to create a summary of the data as shown in the screenshot. The first three boxes have simple calculations and I've edited the interactions so that the value in 'Postgraduate Course' slicer does not interfere with them.

The value I'm struggling to calculate is for the box on the bottom right. This value should be = Number of students taking the specified undergraduate and postgraduate courses/ Number of students taking the specified undergraduate course i.e. 9/642 = 1.40%. Unfortunately, by setting the second slicer ('Postgraduate Course') I filter the records and end up with a value of 100%.

 

I understand that the measure will require different filters for the numerator and denominator of the calculation. Is it possible to do this in Power BI, and to do it in a dynamic way so that the values update based on the selections in the slicers (i.e. without creating a pre-computed table of all combinations)?

Thanks in advance 

[Anonymous · Accepted solution]

Hi, it sounds like you'll likely need to use an ALL() statement for the denominator. It would look like this:

Measure = 
DIVIDE(
[Graduate Count (Specified)],
CALCULATE([Undergraduate Count], ALL(table[post graduate course])
)

This will clear the filter context of the 2nd slicer only and only for the denominator, and allow the other slicers to affect the data.

[Anonymous]

Hi @Anonymous,

Thank you for your response - that solution worked perfectly!

Just an extension question - I want to add another two overlapping slicer for the Faculties which would help refine the sets for the Undergraduate and Postgraduate courses. The problem I have is that the conversion calculation then doesn't work when anything from the Faculty slicer is selected. I have an example I can share to demonstrate the issue, otherwise I can explain it in more detail if more information is needed.

[Anonymous]

Has this problem been addressed? Can you send the example and I can give you my best solution?

[Anonymous]

Hi @Anonymous ,

Yes, it was actually really simple. I didn't realise that you can provide two parameters to the ALL command - this was all I needed to solve the problem.

Thank you for chasing this up.

Best Wishes,