Matrix Conditional formating based on row value

[Anonymous]

Hello everybody,

I have a matrix table where:

• Rows shows different business process of the company
• Columns shows the availability of that process for each month
• Values: meausure of the availability 

The thing is that I need to have different background formating for the values depeding of each row of the Matrix (business service).

For example, for the business services A, Matrix cell value will be red if the measure is below 99,7 % . But for the business services C, Matrix cell value will be red if the measure is below 99,8 % , or red if below 99,5 % in case of business process D.

After doing some reseearch  I have managed to work it out by using rule background formatting 

Based on the following meausure: 

Availab. Color Monthly = MAXX(‘Table’,

IF(‘Table’[Business Process]="Business Process_A" && [Availab. Monthly] < 0.997,1,

    IF(‘Table’[Business Process]= " Business Process_B" && [Availab. Monthly] < 0.997,2,

    IF(‘Table’[Business Process]=" Business Process_C" && [Availab. Monthly] < 0.998,3,

    IF(‘Table’[Business Process]=" Business Process_D" && [Availab. Monthly] < 0.995,4,

               …

               …              

    IF(‘Table’[Business Process]=" Business Process_M" && [Availab. Monthly] < 0.995,13)))

However is not working properly as there are some values that are not being red coloured when it should (orange circle) as the picture attached shows. 

Does anyone knows what could be happening?. 

Thanks a lot for your help!

[wasirafi]

@lbendlin  if i want to get another color "green" for values greater than 99.5, i modified the DAX to below

Availab. Monthly_Color = SWITCH(
    MAX('Incidents Analysis'[Bussiness Process]),
    "BUSINESS PROCESS A",IF([Availab. Monthly]<99.7,"red"),
    "BUSINESS PROCESS A",IF([Availab. Monthly]>99.7,"Green")    
)

 However i dont get the green color. what am i doing wrong in this.

[lbendlin]

Availab. Monthly_Color = SWITCH(
    MAX('Incidents Analysis'[Bussiness Process]),
    "BUSINESS PROCESS A",IF([Availab. Monthly]<99.7,"red","Green")    
)

[Anonymous]

Hi @Anonymous ,

Please create a new measure like:

Availab. Color Monthly = 
SWITCH(
    MAX('Table'[Business Process]),
    "Business Process_A",IF([Availab. Monthly]<0.997,"red"),
    "Business Process_C",IF([Availab. Monthly]<0.998,"red"),
    "Business Process_D",IF([Availab. Monthly]<0.995,"red"),
    <the other condtions>
)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Anonymous]

hi @Anonymous 

Thanks a lot for your help. I have created the new measure and the result it is quite better. There is just one value that is not red colored, quite wierd...

Please check the picture attached

[Anonymous]

Hi @Anonymous ,

I'm not sure. 
Is there no value in this row? What is your modified measure?

It would be helpful to consider providing a file that does not contain private data.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Anonymous]

 @Hi @Anonymous 

Please find below the meausure.

Availab. Monthly_Color = SWITCH(
    MAX('Incidents Analysis'[Bussiness Process]),
    "BUSINESS PROCESS_A",IF([Availab. Monthly]<0.997,"red"),
    "BUSINESS PROCESS_B",IF([Availab. Monthly]<0.997,"red"),
    "BUSINESS PROCESS_C",IF([Availab. Monthly]<0.998,"red"),
    "BUSINESS PROCESS_D",IF([Availab. Monthly]< 0.995,"red"),
    "BUSINESS PROCESS_E",IF([Availab. Monthly]<0.998,"red"),
    "BUSINESS PROCESS_F",IF([Availab. Monthly]<0.997,"red"),
    "BUSINESS PROCESS_G",IF([Availab. Monthly]< 0.998,"red"),
    "BUSINESS PROCESS_H",IF([Availab. Monthly]< 0.998,"red"),
    "BUSINESS PROCESS_I",IF([Availab. Monthly]<0.999,"red"),
    "BUSINESS PROCESS_J",IF([Availab. Monthly]<0.998,"red"),
    "BUSINESS PROCESS_K",IF([Availab. Monthly]<0.995,"red"),
    "BUSINESS PROCESS_L",IF([Availab. Monthly]< 0.995,"red"),
    "BUSINESS PROCESS_M",IF([Availab. Monthly]< 0.995,"red")
)

 The problem only appears with the Row "Business Process F". 

Incident	Opened date	Bussiness Process	SLA Impact sg	Year	Week	Month	Day	Impacted Time	Availab.
INC000001543806	10/10/2023	BUSINESS PROCESS A	410	2023	42	10	10/10	0:06:50	99,98%
INC000001543806	10/10/2023	BUSINESS PROCESS C	335	2023	42	10	10/10	0:05:35	99,99%
INC000001543152	10/10/2023	BUSINESS PROCESS M	3120	2023	42	10	10/10	0:52:00	99,88%
INC000001543152	10/10/2023	BUSINESS PROCESS K	3120	2023	42	10	10/10	0:52:00	99,88%
INC000001543665	10/10/2023	BUSINESS PROCESS B	840	2023	42	10	10/10	0:14:00	99,97%
INC000001543383	09/10/2023	BUSINESS PROCESS A	14400	2023	42	10	10/9	4:00:00	99,44%
INC000001543688	09/10/2023	BUSINESS PROCESS C	1200	2023	42	10	10/9	0:20:00	99,95%
INC000001540831	03/10/2023	BUSINESS PROCESS A	10800	2023	41	10	10/3	3:00:00	99,58%
INC000001540020	29/09/2023	BUSINESS PROCESS C	17100	2023	40	9	9/29	4:45:00	99,34%
INC000001540020	29/09/2023	BUSINESS PROCESS A	12600	2023	40	9	9/29	3:30:00	99,51%
INC000001539379	29/09/2023	BUSINESS PROCESS F	2400	2023	40	9	9/29	0:40:00	99,91%
INC000001539379	29/09/2023	BUSINESS PROCESS H	2400	2023	40	9	9/29	0:40:00	99,91%
INC000001538799	28/09/2023	BUSINESS PROCESS F	0	2023	40	9	9/28	0:00:00	100,00%
INC000001537420	25/09/2023	BUSINESS PROCESS A	900	2023	40	9	9/25	0:15:00	99,97%
INC000001537139	25/09/2023	BUSINESS PROCESS C	34200	2023	40	9	9/25	9:30:00	98,68%
INC000001537139	25/09/2023	BUSINESS PROCESS A	34200	2023	40	9	9/25	9:30:00	98,68%
INC000001536168	22/09/2023	BUSINESS PROCESS F	11700	2023	39	9	9/22	3:15:00	99,55%
INC000001536168	22/09/2023	BUSINESS PROCESS H	11700	2023	39	9	9/22	3:15:00	99,55%
INC000001533830	22/09/2023	BUSINESS PROCESS K	8100	2023	39	9	9/22	2:15:00	99,69%
INC000001533830	22/09/2023	BUSINESS PROCESS M	8100	2023	39	9	9/22	2:15:00	99,69%
INC000001533550	21/09/2023	BUSINESS PROCESS M	9000	2023	39	9	9/21	2:30:00	99,65%
INC000001533550	21/09/2023	BUSINESS PROCESS K	9000	2023	39	9	9/21	2:30:00	99,65%
INC000001529754	08/09/2023	BUSINESS PROCESS C	6300	2023	37	9	9/8	1:45:00	99,76%
INC000001529754	08/09/2023	BUSINESS PROCESS C	12600	2023	37	9	9/8	3:30:00	99,51%

Thanks.

[Anonymous · Accepted solution]

Hi @Anonymous ,

I noticed that the data type of [Availab.] is the decimal number.

99.98 converted to percentage format is 9980%

I edited the measure and checked to see if this was causing the problem.

Availab. Monthly_Color = SWITCH(
    MAX('Incidents Analysis'[Bussiness Process]),
    "BUSINESS PROCESS A",IF([Availab. Monthly]<99.7,"red"),
    "BUSINESS PROCESS B",IF([Availab. Monthly]<99.7,"red"),
    "BUSINESS PROCESS C",IF([Availab. Monthly]<99.8,"red"),
    "BUSINESS PROCESS D",IF([Availab. Monthly]<99.5,"red"),
    "BUSINESS PROCESS E",IF([Availab. Monthly]<99.8,"red"),
    "BUSINESS PROCESS F",IF([Availab. Monthly]<99.7,"red"),
    "BUSINESS PROCESS G",IF([Availab. Monthly]<99.8,"red"),
    "BUSINESS PROCESS H",IF([Availab. Monthly]<99.8,"red"),
    "BUSINESS PROCESS I",IF([Availab. Monthly]<99.9,"red"),
    "BUSINESS PROCESS J",IF([Availab. Monthly]<99.8,"red"),
    "BUSINESS PROCESS K",IF([Availab. Monthly]<99.5,"red"),
    "BUSINESS PROCESS L",IF([Availab. Monthly]<99.5,"red"),
    "BUSINESS PROCESS M",IF([Availab. Monthly]<99.5,"red")
)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Anonymous]

Hi  @Anonymous 

That´s not the problem. It is not related with the data type. it seems that in that particular case, 

BUSINESS PROCESS F",IF([Availab. Monthly]<99.7,"red"),

and for that specific month (March --> Availab. = 99.53) the formula is not working...   It is quite strange.

Any idea? 

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...