Forum Discussion

giramswa's avatar
giramswa
Icon for Helper II rankHelper II
4 years ago
Solved

Conditional formatting(Font Color) of row values in Matrix Visual with different target for each row

Hi All,

 

I have a matrix visual with Rows and Values as below:

 

 

I have not used any columns here. [3 months], [6 months] and [12 months] are separate measures that are concatenating values from three individual measures each (where actual calculations are done), e.g. 

[12 months] = IF(SELECTEDVALUE(Main_Table[Metric Name]) = "### COMPLIANCE", Main_Table[### Compliance(%)_12 months_union]&" %", IF(SELECTEDVALUE(Main_Table[Metric Name])="OVERAGE", Main_Table[Overage(%)_12 months_union]&" %", IF(SELECTEDVALUE(Main_Table[Metric Name])="$$$ TIMELINESS", Main_Table[$$$_Timeliness_12 months_union]&" Days", IF(SELECTEDVALUE(Main_Table[Metric Name]) = "$$$ ACCURACY", Main_Table[$$$_Accuracy(%)_12 months_union]&" %")))),


logic similar to above is used in measure [3 months] and [6 months].

I need to change font color for the four 'Metric' with different targets:

1. Overage - annual aggregate should be below 14% = GREEN, else RED.

2. $$$ accuracy - GREEN = within 20% (20% to -20%) annually, else RED

3. $$$ Timeliness - 

           Green (values submitted within 2 weeks) 85%+
           Yellow (values last submitted greater than 2 weeks ago, less than 4 weeks ago) 10%-84%
           Red (values submitted greater than 4 weeks ago) 5%

4. ### Compliance - 80% anually = GREEN, else RED

I tried conditional formatting cell value, as seen above for '### Compliance' but its applicable for single row as I can use only one measure. But I have three measure to display values across period of 3, 6 and 12 months. How can I achive this?

Expected Outcome:

 

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi giramswa ,

     

    Please create 3 measures seperately for [3 months],[6 months],[12 months]

    For 3 months color = SWITCH(MAX('Table'[Metric]),
    "overage",IF( ABS([3 months])>14,"Red","Green"),
    "Accuracy",IF( ABS([3 months])>20,"Red","Green"),
    "Timeliness",IF([3 months]>0.85,"Green",IF([3 months]>0.1,"Yellow","Red")),
    "Compliance",IF([3 months]>0.8,"Green","Red")
    )

    ......

     

     

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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi giramswa ,

     

    Please create 3 measures seperately for [3 months],[6 months],[12 months]

    For 3 months color = SWITCH(MAX('Table'[Metric]),
    "overage",IF( ABS([3 months])>14,"Red","Green"),
    "Accuracy",IF( ABS([3 months])>20,"Red","Green"),
    "Timeliness",IF([3 months]>0.85,"Green",IF([3 months]>0.1,"Yellow","Red")),
    "Compliance",IF([3 months]>0.8,"Green","Red")
    )

    ......

     

     

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

  • Anonymous - your solution was Bang on! It worked perfectly. Thank you so much for your help.