How to create a gradient color scale on one row level in Power BI Matrix

[LamSar]

Good afternoon,

I have a question concerning conditional formatting in a Matrix.

I have the following matrix:

On the rows I have Shop type, Shop, function

On the columns I have Year, month

Values= Measure that calculates the hours

The requirement is to have a gradient color scale on the rows of the shops (highlighted in yellow) where green is zero hours and it goes to red for the max hours. 

I tried using this measure:

Color scale =

VAR SummaryTable =

CALCULATETABLE(

ADDCOLUMNS(

SUMMARIZE(Tijd, DatumVerkoop[Jaar], DatumVerkoop[MaandNaam], Shop[Name]), "AantalUren", [_AlleUren Y (U)]),

ALLSELECTED())

Var MaxValue =

MAXX(SummaryTable, [AantalUren])

Var MINXValue =

MINX(SummaryTable, [AantalUren])

VAR Range =  MaxValue - MINXValue

Var Hue = ROUND(DIVIDE([_AlleUren Y (U)] - MINXValue,Range),2)

Var Colour = If(ISFILTERED(Shop[Name]) && [_AlleUren Y (U)] >= MINXValue && [_AlleUren Y (U)] <= MaxValue,"hsla("& Hue & ", " & "100%" & ", " & "90%" & ", " & 1 & ")", blank())

RETURN

Colour

But as you can see in the screenshot it only shows a color for the lowest value and the highest value.

I also need to be able to set the colors for zeo hours (green) to max hours red.

The color scale should look something like this:

  Any ideas on how to fix this?

[v-shex-msft · Accepted solution]

HI @LamSar,

AFAIK, you can’t be accurate to control the detail color in 'color scale' mode.

I’d like to suggest you use measure formula to parameterize the color code with the variable calculation result and use this formula as conditional formatting with expression mode.

For dynamic the colors based on calculation result, you can take a look at the following video about use Dax formula to apply HSLA color to visual if helps:

Advanced Coloring in Power BI For ALL VISUALS using HSLA Colors and DAX - YouTube

Regards,

Xiaoxin Sheng