Forum Discussion
Conditional Formatting Dax
Hi Anonymous
I need some help in defining logic for confitional formatting for matrix visual
I have below data
| Row Labels | Sunday | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday |
| 7 AM | 18 | 20 | 17 | 16 | 11 | 46 | 11 |
| 8 AM | 27 | 45 | 32 | 27 | 22 | 94 | 29 |
| 9 AM | 57 | 90 | 94 | 32 | 1562 | 166 | 39 |
| 10 AM | 63 | 131 | 137 | 133 | 8525 | 250 | 52 |
| 11 AM | 47 | 120 | 54 | 68 | 2086 | 127 | 34 |
| 12 PM | 29 | 89 | 63 | 51 | 1003 | 93 | 34 |
| 1 PM | 30 | 71 | 51 | 42 | 694 | 100 | 20 |
| 2 PM | 23 | 87 | 52 | 62 | 636 | 80 | 37 |
| 3 PM | 25 | 59 | 48 | 44 | 486 | 82 | 27 |
| 4 PM | 29 | 55 | 59 | 36 | 338 | 104 | 26 |
| 5 PM | 24 | 58 | 51 | 39 | 321 | 72 | 36 |
| 6 PM | 39 | 42 | 42 | 43 | 287 | 77 | 33 |
| 7 PM | 31 | 31 | 29 | 21 | 184 | 42 | 25 |
| 8 PM | 24 | 30 | 23 | 18 | 195 | 47 | 29 |
| 9 PM | 39 | 39 | 29 | 19 | 199 | 48 | 37 |
| 10 PM | 41 | 28 | 20 | 17 | 182 | 64 | 42 |
| 11 PM | 29 | 23 | 20 | 10 | 97 | 64 | 33 |
I need to apply conditional formatting on each cell of matrix
I want to divide the values of matrix in 5 differnet percentage group which is
0-20%
21 to 40%
41 to 60%
61 to 80%
81 to 100%
and apply a differnet background color for each ranges.
based on the dataset, the 20 percentile is 27, 40 percentile is 37.4, 60 percentile is 52, 80 percentile is 95.2 and 100 percentile is 8525
so value less than 27 should be in different color and so on
Hi kapildua16 Another way is through a measure you could also try if you want
Create a DAX Measure: Define a measure that assigns a numeric category (1–5) based on the value in the matrix.
PercentileCategory = VAR CurrentValue = SELECTEDVALUE(MatrixTable[Value]) -- Replace with your column name RETURN SWITCH( TRUE(), CurrentValue < 27, 1, -- 0-20% CurrentValue <= 37.4, 2, -- 21-40% CurrentValue <= 52, 3, -- 41-60% CurrentValue <= 95.2, 4, -- 61-80% TRUE(), 5 -- 81-100% )Apply Conditional Formatting:
- Go to your matrix visual, select the field you want to format, and navigate to Conditional Formatting > Background Color.
- Choose Format by: Field Value.
- Select the PercentileCategory measure.
- Assign colors to categories:
- Category 1 (0–20%): Light color.
- Category 2 (21–40%): Slightly darker shade.
- Category 3 (41–60%): Medium color.
- Category 4 (61–80%): Darker color.
- Category 5 (81–100%): Bright or bold color.
Hi, kapildua16
I wish you all the best. Previously MattiaFratello and Akash_Varuna have provided a solution to help you solve the problem. Since we haven't heard back from you yet, I'd like to confirm if you've successfully resolved this issue or if you need further help?
If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.Best Regards
Jianpeng Li
6 Replies
Hi kapildua16,
Please right-click on the column you used in Values (in your matrix) -> Conditional Formatting -> Background Color
If I answered your question please feel free to mark it as an answer!Hi kapildua16 Another way is through a measure you could also try if you want
Create a DAX Measure: Define a measure that assigns a numeric category (1–5) based on the value in the matrix.
PercentileCategory = VAR CurrentValue = SELECTEDVALUE(MatrixTable[Value]) -- Replace with your column name RETURN SWITCH( TRUE(), CurrentValue < 27, 1, -- 0-20% CurrentValue <= 37.4, 2, -- 21-40% CurrentValue <= 52, 3, -- 41-60% CurrentValue <= 95.2, 4, -- 61-80% TRUE(), 5 -- 81-100% )Apply Conditional Formatting:
- Go to your matrix visual, select the field you want to format, and navigate to Conditional Formatting > Background Color.
- Choose Format by: Field Value.
- Select the PercentileCategory measure.
- Assign colors to categories:
- Category 1 (0–20%): Light color.
- Category 2 (21–40%): Slightly darker shade.
- Category 3 (41–60%): Medium color.
- Category 4 (61–80%): Darker color.
- Category 5 (81–100%): Bright or bold color.
Hi, kapildua16
I wish you all the best. Previously MattiaFratello and Akash_Varuna have provided a solution to help you solve the problem. Since we haven't heard back from you yet, I'd like to confirm if you've successfully resolved this issue or if you need further help?
If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.Best Regards
Jianpeng Li
Hi kapildua16 ,
Thank you for reaching out to the Microsoft Community Forum.
Step-by-step DAX Measure for Conditional Formatting:
1.DAX measure that categorizes the values into those 5 percentile buckets:
Cell Color Group =
VAR CellValue = SELECTEDVALUE('YourTable'[Value])
RETURN
SWITCH(
TRUE(),
CellValue < 27, "#d73027", -- 0-20%
CellValue < 37.4, "#fc8d59", -- 21-40%
CellValue < 52, "#fee08b", -- 41-60%
CellValue < 95.2, "#d9ef8b", -- 61-80%
CellValue <= 8525, "#91cf60", -- 81-100%
"#FFFFFF"
)Applying the Conditional Formatting: Select your Matrix visual.
Click on the values dropdown in the Visualizations pane. Choose Conditional Formatting > Background color. Select Field value. Pick the Cell Color Group measure from the dropdown.
2.Show Percentile Group: If you want to show which group each value falls into as a label:
Percentile Group Label =
VAR CellValue = SELECTEDVALUE('YourTable'[Value])
RETURN
SWITCH(
TRUE(),
CellValue < 27, "0-20%",
CellValue < 37.4, "21-40%",
CellValue < 52, "41-60%",
CellValue < 95.2, "61-80%",
CellValue <= 8525, "81-100%",
"N/A"
)If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.
Thank youHi kapildua16 ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and consider giving a KUDOS. Feel free to reach out if you need further assistance.Regards,
Dinesh
Hi kapildua16 ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and consider giving a KUDOS. Feel free to reach out if you need further assistance.Regards,
Dinesh
