Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Matrix Conditional formating based on row value

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!

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

9 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    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-Forum/ba-p/963216
    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/1447523

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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

  • wasirafi's avatar
    wasirafi
    Helper I
    2 years ago

    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's avatar
      lbendlin
      Super User
      2 years ago
      Availab. Monthly_Color = SWITCH(
    MAX('Incidents Analysis'[Bussiness Process]),
    "BUSINESS PROCESS A",IF([Availab. Monthly]<99.7,"red","Green")    
)