Conditional Formatting Dax

[kapildua16]

Hi @Everyone 
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

[Akash_Varuna · Accepted solution]

Hi @kapildua16 Another way is through a measure you could also try if you want 

1. 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%
       )

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

[v-dineshya]

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 you

[v-dineshya]

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

[v-dineshya]

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

[Anonymous · Accepted solution]

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

[Akash_Varuna · Accepted solution]

Hi @kapildua16 Another way is through a measure you could also try if you want 

1. 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%
       )

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

[MattiaFratello]

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!