Color apply horizontally

[RameshPachunuri]

How do we apply color in horizantal way like Row wise? For example we have a table shown below and it has values from Col1 to 5 and I want to apply the color variations based on the value like if it is max then it should be red and min value should apply green and all middle values should apply orange. How can we achieve that?

[RameshPachunuri · Accepted solution]

Thank you for your repsonse.

It seems in your col2 to 5 are coming from seperate columns. But myside columns data is coming from single column from Tabl as shown below.

Here I am splitting the values based on Column1 and DateTime then showing in the matrix as shown below. Then I want to apply colors in Matrix tables based on Max, min and Middle values with respect to Column1 and DateTime columns. Please let me know how can we achieve this?

[v-frfei-msft]

Hi @RameshPachunuri ,

I have created a sample for your refernce. Please check the following steps as below.

1, Unpivot the table as below.

The M code is here:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI00DMFUaZgyhKILYCsWJ1oJScg28gAIWcBJs3Bcs4gYWOQAoickRmCAsm7gOWRTUamQCpcwaZDxAz1zBGUpVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type number}, {"Column3", type number}, {"Column4", type number}, {"Column5", type number}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Column1"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

2. Close and aplly, then wen can create a measure based on the transformed table.

Measure = 
VAR A =
    CALCULATE ( MAX ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[Column1] ) )
VAR MIND =
    CALCULATE ( MIN ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[Column1] ) )
RETURN
    IF (
        SUM ( 'Table'[Value] ) = A,
        "Red",
        IF ( SUM ( 'Table'[Value] ) = MIND, "Green", "Orange" )
    )

3. In the end, we can set conditional formatting of the background based on the measure like that.

For more details, please check the pbix as attached.

[RameshPachunuri · Accepted solution]

Thank you for your repsonse.

It seems in your col2 to 5 are coming from seperate columns. But myside columns data is coming from single column from Tabl as shown below.

Here I am splitting the values based on Column1 and DateTime then showing in the matrix as shown below. Then I want to apply colors in Matrix tables based on Max, min and Middle values with respect to Column1 and DateTime columns. Please let me know how can we achieve this?

[v-frfei-msft · Accepted solution]

Hi @RameshPachunuri ,

Here we go.

Measure = VAR MAXV = CALCULATE(MAX('Table'[Column 2]),ALLEXCEPT('Table','Table'[Column 1]))
VAR MINV = CALCULATE(MIN('Table'[Column 2]),ALLEXCEPT('Table','Table'[Column 1]))
RETURN
IF(SUM('Table'[Column 2]) = MAXV , "Red", IF(SUM('Table'[Column 2]) = MINV,"Green", "Orange"))

Pbix as attached.

[TomMartens]

Hey,

from your table it looks like the columns Column2 to Column5 contain numerical values.

If your are able to convert these columns into an attribute / value  combination meaning (converting a wide table format into a long table format), e.g by using the Unpivot function from Power Query:

https://support.office.com/en-us/article/unpivot-columns-power-query-0f7bad4b-9ea1-49c1-9d95-f588221...

, you can apply the conditional formatting  also row-wise.

Hopefully this provides you with a new idea, to tackle your challenge.

Regards,

Tom

[Anonymous]

Hi @RameshPachunuri ,

Conditional formatting for table in horizontally is not possible.

Best Regards,
Mail2inba4

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.