Filter by Matrix Subtotals

[PBInewbie17]

I have a matrix in Power BI that includes PHA and Development as Rows. I want to filter the matrix to only show PHAs with a MASS Occupancy Rate below 95.50%, but I want to include ALL Developments under a PHA, regardless of a Development's MASS Occupancy Rate. MASS Occupancy Rate is a calculated measure in a separate Measures table. I do not have a separate MASS Occupancy Rate calculation that is only PHAs.

The attached image provides a specific image of what I'm trying to achieve. Thank you

[isjoycewang]

Hi @PBInewbie17,

Demo file attached.

Please try below DAX and apply it to your matrix visual:

Filter = IF( CALCULATE( [Rate], ALLEXCEPT('Table','Table'[PHA])) < 0.955 , 1, 0)

Best Regards,

Joyce

[PBInewbie17 · Accepted solution]

Joyce, I tried your solution, but the filter is still excluding all Developments with MASS Occupancy Rates below 95.50%, and not just the PHA. Please see below. Before the filter, the PHA for OR008 has a MASS Occupancy Rate of 94.57%. Because this is below 95.50%, it should remain on the matrix, along with EVERY Development beneath it, regardless of what the MASS Occupancy Rate is for those Developments.'