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

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

2 Replies

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