I am using the following measure in my model:
=maxx(all(Russell_Group[Provider short name]),[Student FTE (Total)])-[Student FTE (Total)]
I have the 'Providers' as rows in my pivot table and the purpose is to show the difference in Student FTE between each provider and top-ranked provider.
My problem is that the 'all' function within the 'maxx' calculation is causing all providers to be included in my pivot table, even those that don't have any Student FTEs, which makes it look a mess.
Is there an alternative to 'all' that would only return the providers that already appear on the pivot table based on existing filters (i.e. ~ 'selected' providers).
Hope this makes sense! Many thanks in advance.
=maxx(all(Russell_Group[Provider short name]),[Student FTE (Total)])-[Student FTE (Total)]
I have the 'Providers' as rows in my pivot table and the purpose is to show the difference in Student FTE between each provider and top-ranked provider.
My problem is that the 'all' function within the 'maxx' calculation is causing all providers to be included in my pivot table, even those that don't have any Student FTEs, which makes it look a mess.
Is there an alternative to 'all' that would only return the providers that already appear on the pivot table based on existing filters (i.e. ~ 'selected' providers).
Hope this makes sense! Many thanks in advance.