Forum Discussion

GarethW's avatar
GarethW
Frequent Visitor
6 years ago
Solved

Conditional formatting of Matrix based on Total average

Hello I am new to PowerBI and am in the process of migrating old manual reports to it.   One of these reports shows share of sales, by month (column) by product type (rows).   This has a hea...
Need Help
  • MFelix's avatar
    MFelix
    6 years ago

    Hi GarethW ,

     

    First of all let me apologize for the late response I must have missed this post on the multiple notifications I have.

     

    What I think I was abble to get it to work I have created 3 measures (this simplifies the tough process):

    % per category/month =
DIVIDE (
    COUNT ( 'Table'[Region] );
    CALCULATE ( COUNT ( 'Table'[Region] ); ALLSELECTED ( 'Table'[Region] ) )
)



% Total/region =
VAR temp_table =
    FILTER (
        SUMMARIZE (
            ALLSELECTED ( 'Table' );
            'Table'[Region];
            "@count"; COUNT ( 'Table'[Region] )
        );
        'Table'[Region] = MAX ( 'Table'[Region] )
    )
VAR total_table =
    SUMMARIZE (
        ALLSELECTED ( 'Table' );
        'Table'[Region];
        "@count"; COUNT ( 'Table'[Region] )
    )
RETURN
    DIVIDE ( SUMX ( temp_table; [@count] ); SUMX ( total_table; [@count] ) )



% variation = [% per category/month]-[% Total/region]

     

    Now use the last measure to create your condittional formatting.

     

    As you can see on the image below the values that are equal to the region total are blank below are red and above are green.

     

    Check also the PBIX file attached.

     

    Once more I'm very sorry for the delay in the answer.

Noredlac86's avatar
Noredlac86
Helper I
3 years ago

Hi MFelix 

I was wondering if its possible to change the color for the "ROWS". I mean in your example should be: "Region".

I have this:

 


and i want to change the color of the rows (I mean the text in the first column, A) 0-15Days.... etc.

Im using matrix chart, if i use table chat, will work, but i need use matrix.

in ther words, i need that the firs row, should be totally green, the secong row yelow and etc

MFelix's avatar
MFelix
Super User
3 years ago

Hi Noredlac86 ,

 

In this case you need to add an additional statment to your calculation in order to check if you are in the line of A, B or C.

 

You need to create a measure similar to this:

 

Color coding by row = SWITCH(SELECTEDVALUE('Table'[Region]),
                        "A", "Green",
                        "B", "Blue")

Then on the condittional formatting  select the Field value

Result below:

You can also use the HEX codes for the coloring.

 

  • Noredlac86's avatar
    Noredlac86
    Helper I
    3 years ago

    Thanks MFelix for take your time and reply your message however is missing the first column "Region"


    How can put the same color for the complete row, including all columns.




  • MFelix's avatar
    MFelix
    Super User
    3 years ago

    Hi Noredlac86

    On the row header you cannot make the condittional format,

    Only workaround is to create a measure with the same value has the row header and use it has the first value of your matrix then hide the row header by decreasing the size of the column