Forum Discussion

RameshPachunuri's avatar
RameshPachunuri
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago
Solved

Color apply horizontally

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 ...
Need Help
  • RameshPachunuri's avatar
    RameshPachunuri
    6 years ago

    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's avatar
    v-frfei-msft
    6 years ago

    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.

     

v-frfei-msft's avatar
v-frfei-msft
Icon for Community Support rankCommunity Support
6 years ago

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's avatar
RameshPachunuri
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

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's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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.