Forum Discussion

PBInewbie17's avatar
PBInewbie17
Helper I
2 years ago
Solved

Filter by Matrix Subtotals

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 un...
Need Help
  • PBInewbie17's avatar
    PBInewbie17
    2 years ago

    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.'

isjoycewang's avatar
isjoycewang
Solution Supplier
2 years ago

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's avatar
PBInewbie17
Helper I
2 years ago

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.'