Comparing columns of same table and changing colour accordingly

[SubhamKumar]

Hi Folks,

I have an scenario where I need to transform this data.

into this

.

Let me explain how this is working.

1st column greyed out 

2nd column -compare with first column,if greater then green ,if smaller then red

nth column - compare with n-1 column,if greater then green ,if smaller then red

where n=2,3,4,... etc.

I am unable to find an solution for this.

Would be great if you can help me out.

[lbendlin · Accepted solution]

Power BI has no concept of column numbers or row numbers.  You need to provide that as part of your ETL.

First step as always is to unpivot the source data into something usable.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PY27DcAwCERXiahTYD4G9khnuUj2HyJApDT3JPS4Wwuu+zkGnBCDKOGRQSqWmMwFFWLY56dS30uaZoWQoQkz8YQwxu9yVblhW1gj3p9E2r2mCHu/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Tab = _t, #"Day 1" = _t, #"Day 2" = _t, #"Day 3" = _t, #"Day 4" = _t, #"Day 5" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Tab"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Day"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Day ID", each Int16.From (Text.Replace([Day],"Day ",""))),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Day ID", Int64.Type}, {"Value", Currency.Type}})
in
    #"Changed Type"

Then you can create calculated columns or (likely preferred) measures that implement your logic.

Measure = 
var i = max('Table'[Day ID])
return if(i=1,"grey",if(CALCULATE(sum('Table'[Value]),REMOVEFILTERS('Table'[Day]),'Table'[Day ID]=i-1)>sum('Table'[Value]),"red","green"))

And lastly you use that measure to apply the conditional formatting.

see attached.