Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Formatiting matrix

Hello,

 

I have table of data as below :

CenterMachineinstallcheckedFilterAccountLockAccountUnlockpasswordcontrolpasswordReuseauthorizedServiceLogtimeout
apttsv1sxmus1OKOKOKOKOKKOOKOKOKOKOK
apttsv1sxmus2OKOKOKOKOKKOOKOKOKKOOK
apttsv1sxmus3OKOKOKOKOKKOOKOKOKKOOK
apttsv1sxmus4OKOKOKOKOKKOOKOKOKKOOK
apttsv1sxmus5OKOKOKOKOKKOOKOKOKKOOK
apttsv1sxmus6OKOKOKOKOKKOOKOKOKKOOK
apttsv1sxmus7OKOKOKOKOKKOOKOKOKKOOK
apttsv1sxmus8OKOKOKOKOKKOOKOKOKKOOK
apttsv2sxmus9OKOKOKOKOKKOOKOKOKKOOK
apttsv2sxmus10OKOKOKOKOKKOOKOKOKKOOK
apttsv2sxmus11OKOKOKOKOKKOOKOKOKKOOK

 

I would like to formatting the matrix below : 

I am trying to format the cell:

- OK : Green

- KO : Red

- Machine  : Green if all OK

- Machine : Red if there is a KO

-Center : Green if all Machine Green (All OK)

- Center: Red if any Machine Red (If any KO)

Could you please advise ? 

Thank you in advance.

Tg

 

Need Help
  • pi_eye's avatar
    pi_eye
    3 years ago

    Further to this Anonymous 

     

    I do have a potential work-around.

    First, structure the data by unpivoting it in Power Pivot and adding a column that is "1" for KO and "0" for OK:

    This will then make it much easier to create a Matrix using those values!

    Then, create a measure that summarises "hasKO" by making a sum of the values. We now know that if the sum = 0 there are no KO's and anything else means that there is at least one.

     

    anyKO = sum(Sheet1[hasKO])
     
    Use this measure in the conditional formatting to colour values with 0 as green and anything else as red:

    This will also colour the value totals. Using the value totals as a comparison will give you an indication of the machine and server situations:

     

     

     

     HTH

     

    Pi

     

     

     

     

     

     

3 Replies

  • pi_eye's avatar
    pi_eye
    Resolver IV
    3 years ago

    Hi Tg!

     

    Formatting Cell values is simple, you can format them conditionally using a column value. Here I have created a column that corresponds to "Log" and returns a colour:

    First Log Color = If(Sheet1[Log]="KO","Red","Green")
    Apply this to the formatting of the Log column by clicking the drop down next to the value name, and navigating to "background color" under "conditional formatting". 
    In the resulting dialogue, choose Field Value as the format Style and select the new [First Log Color] field that has been created:
        

    When applied, this will now color your "Log" field appropriately

     

    To apply this to the whole visual you will need to create one for each column in the Matrix 

     

    For the Centre and Machine values, I haven't been able to find a way to colour these conditionally, yet. There is currently an idea being upvoted if you would like to partake:

    https://ideas.powerbi.com/ideas/idea/?ideaid=28bbb6fd-11d5-4713-aa23-030ad77d5d4f

     

    Regards,

     

    Pi

     

     

  • pi_eye's avatar
    pi_eye
    Resolver IV
    3 years ago

    Further to this Anonymous 

     

    I do have a potential work-around.

    First, structure the data by unpivoting it in Power Pivot and adding a column that is "1" for KO and "0" for OK:

    This will then make it much easier to create a Matrix using those values!

    Then, create a measure that summarises "hasKO" by making a sum of the values. We now know that if the sum = 0 there are no KO's and anything else means that there is at least one.

     

    anyKO = sum(Sheet1[hasKO])
     
    Use this measure in the conditional formatting to colour values with 0 as green and anything else as red:

    This will also colour the value totals. Using the value totals as a comparison will give you an indication of the machine and server situations:

     

     

     

     HTH

     

    Pi

     

     

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thank you for your suggestion.