Conditional formatting related to row values instead of columns

[gustavo80]

Hi! I need to set background color in every row with this criteria: darkest colour to higher value IN THE ROW ( note thats this value could belong to diferent measures, kpi1, kpi2, etc), lighter colour to lowest value IN THE ROW, and a degrade between intermediate values.

I noticed that this could be with formational conditional menu on any field of my table, but this set the colours according to lowest/higher value IN THE COLUMN. I need to do this but related to ROW VALUES.

I tried with DAX expresiones, but I failed to calculate a measure "Color" ,for example , that I could use in "Formatting color based on a field". I suppose a kind of division between current value and maxium/minium values could work but I dont know how to calculate this measure 😞

Is possible to do this in PowerBI ? Thanks in advance, 

This is a simplified example of what I need:

[Greg_Deckler · Accepted solution]

@gustavo80 

Interesting. Can be done. Create a disconnected table for your KPI's and a KPI to Show measure like:

KPI to Show = 
    SWITCH(MAX('KPI'[KPI]),
        "kpi1",[kpi1],
        "kpi2",[kpi2],
        "kpi3",[kpi3],
        "kpi4",[kpi4]
    )

Now create a color measure like:

Measure = 
    VAR __Table =
        {
            [kpi1],
            [kpi2],
            [kpi3],
            [kpi4]
        }
RETURN
    COUNTROWS(FILTER(__Table,[Value]<=[KPI to Show]))

Create a matrix with your date in the Rows, KPI from your disconnected table in the Columns and KPI to Show in Values. Base your color coding on Measure. Example attached.

[gustavo80]

Thanks Greg! It seems to be a little tricky, but it works ok for what I need.

Thanks for the quick solution and for the example attached!

Regards,

[shrutiiyer]

Hey @Greg_Deckler , I'm kinda stuck in the same scenario and I have 1000s of rows... Would you happen to know how to go about it, please? 

[Greg_Deckler]

@shrutiiyer - Hard to say, what are the rules for your conditional colors?

[shrutiiyer]

@Greg_Deckler I'm working on a table/,matrix visualization in PowerBI to display website traffic growth by month with rows set to be individual landing pages. There are quite a few pages and we'd like it to visualize the change over time with condition colour formatting of the cells. However, there is a lot of disparity between groups of pages as some are in the 10000+ range with others less than 100. But it's important to visualize the change over time for the lower traffic pages as well as the traffic is significant less in volume but much higher in conversion potential. 

[Greg_Deckler]

Yeah, it's definitely a bit tricky but I'm not sure how else to do it. Disconnected table trick, comes in handy!