Forum Discussion

max_bradley's avatar
max_bradley
Frequent Visitor
3 years ago

Conditional formatting in a matrix with slicers

I have a matrix in which I've duplicated a table of demographic categories, joined them to a table of students belonging to those categories and created a matrix visual. I've swapped the value to show as percent of column total, though row total would produce the same result, showing the percentage of students in the X axis category that also belong to the Y axis category. I've also added a filter (this is important) for year group and school. This result is almost perfect, I'd just like to add conditional formatting but that is set off the underlying number of students, not the percentage, which is a problem. I know that the solution to this is to create a measure that replicates the percentages for the conditional formatting.  I've been able to create this measure, the numerator is a count of the students in the cell, and the denominator is a count outside of the filters applied in the matrix using the ALL function.

 

StdCntDenom = 
    CALCULATE(
    DISTINCTCOUNT(
        Attributes[SchoolAdNo]
    ),
    ALL(
        Attributes
    ),
    Attributes[Attribute]="All",
    NOT(
        ISBLANK(
            StdBasic[Year taught in Code]
        )
    ),
    StdBasic[Year taught in Code]<12
    )

 

However, if I add a slicer that subsets the matrix to just year 10 students for example, the denominator is outside of those filters as well. The result is that no value in the matrix shows 100% any more. If I was using 'view as>percentage of column total' the percentage would be correct but the conditional formatting would be wrong. It seems to me that I need a measure that is unaffected by matrix filters, but is affected by slicer filters. Is this possible?

Need Help

2 Replies

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi max_bradley ,

     

    Yes, you should replace the ALL() with ALLSELECTED() in your code.

    StdCntDenom =
CALCULATE(
    DISTINCTCOUNT( Attributes[SchoolAdNo] ),
    ALLSELECTED( Attributes ),
    Attributes[Attribute] = "All",
    NOT ( ISBLANK( StdBasic[Year taught in Code] ) ),
    StdBasic[Year taught in Code] < 12
)

     

    Best Regards

    Community Support Team _ chenwu zhu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • max_bradley's avatar
      max_bradley
      Frequent Visitor
      3 years ago

      Thank you v-chenwuz-msft this has resolved the bulk of the issue.

       

      It took me a long time to get this working though and I've finally figured out the reason. I was applying your suggestion to the Y axis, because this would be easiest to read. However, the Y axis was set to sort by a different column which causes it to change behaviour. This code;

      StdCntDenom = 
CALCULATE(
    [StdCntNum],
    ALLSELECTED(Attributes[Attribute])
)

      returns the total number of students excluded from the attributes[attribute] filter when attributes[attribute] is not sorted by another column. When it is sorted by another column, the same code returns a value that is filtered by attributes[attribute]. Any idea why this is happening? I would ideally like to sort both axes to keep the categories easy to find.