Forum Discussion

Chantine's avatar
Chantine
Frequent Visitor
2 years ago
Solved

Matrix Conditional Formatting correct in totals but not values

Hi,

 

I am currently building a matrix that has 2 row values and one column. I have formatted this matrix to colour based on whether a role is over/under/within capacity for that month. The current table this is coming from is setup like this:

 

RoleMonth YearTime TypeHours
ConsultantJanuary 2024Project30
ConsultantJanuary 2024Capacity40
ConsultantJanuary 2024Request15

 

The matrix currently looks like this:

 

 

Where the totals (e.g., developer) are correct but the project/request level and grand total on the bottom is incorrect. The values in the matrix are summing hours where time type = Request or Project, and is compared against a measure for Capacity where Time type = Capacity. 

 

Is there anything I'm doing incorrectly? I thought it might be because the capacity measure isn't in the matrix, but then the totals shouldn't be correct either?

 

Many thanks

Need Help
  • Chantine's avatar
    Chantine
    2 years ago

    In case anyone is curious or has the same issue, I found that because my capacity was also marked within the time type column, it was filtering it out in the matrix (as I only added project and request types together). This measure solved the issue for me: 

     

    Capacity = CALCULATE([Time],ALLEXCEPT('Hours Planned','Hours Planned'[Month Year],'Hours Planned'[Role]),'Hours Planned'[Time Type] = "Capacity")
     
    So it only added together capacity lines and kept the month year filter from the matrix, but ignored the time type filter that the matrix was imposing. 

1 Reply

  • Chantine's avatar
    Chantine
    Frequent Visitor
    2 years ago

    In case anyone is curious or has the same issue, I found that because my capacity was also marked within the time type column, it was filtering it out in the matrix (as I only added project and request types together). This measure solved the issue for me: 

     

    Capacity = CALCULATE([Time],ALLEXCEPT('Hours Planned','Hours Planned'[Month Year],'Hours Planned'[Role]),'Hours Planned'[Time Type] = "Capacity")
     
    So it only added together capacity lines and kept the month year filter from the matrix, but ignored the time type filter that the matrix was imposing.