topic Re: Conditional formatting using Row Level comparing with column Header in Desktop

Conditional formatting using Row Level comparing with column Header

harirao — Thu, 01 Oct 2020 15:38:46 GMT

Hello All,
Need your assistance, need to create conditional formatting using Month column row level with column header in power bi.

In attached screen shot (PBI) you can see column header is month i want to do formatting based on this also Excel snapshot visualization looking in power bi.

Eg:

If Apr-20 (month-row) is <= Aug-19 (Column) background should be blue, if Apri-20 > Aug-19 (Column) it should be green, tried with one dax, but not sure if its correct.

Column = if('Final Waterfall Qty'[Month]<='Final Waterfall Qty'[August2019],"Light Blue", IF('Final Waterfall Qty'[Month]>'Final Waterfall Qty'[August2019],"Light Green"))

Is this possible in power bi

Thanks in advance for any help on the topic!

Re: Conditional formatting using Row Level comparing with column Header

MFelix — Thu, 01 Oct 2020 15:52:31 GMT

Hi harirao ,

Are the columns headers values from another column on your model or only name of measures?

If they are values in columns yuo can make a comparision between both by makeing the selection of both values, if however the columns are measures or hard coded name of columns in your data model you then need to replace the second part of your statment by a DATE or another type of syntax that compares with the date of the row something like this:

Column = if('Final Waterfall Qty'[Month]<=DATE(2019,1,1),"Light Blue", "Light Green")

Be aware that I'm assuminmg that your month columns is based on a date if it's only a text your need to make changes to your measure.

Re: Conditional formatting using Row Level comparing with column Header

negi007 — Fri, 02 Oct 2020 09:24:28 GMT

harirao It may be possible to have that type of conditional formating in your matrix table.

Create seperate measure for each month. Like below

Measure 1. Sep'20=CALCULATE(SUM(qty),'Calendar'[Year]=2020,MONTH('Calendar'[Date])=9)

Measure 2. Aug'20=CALCULATE(SUM(qty),'Calendar'[Year]=2020,MONTH('Calendar'[Date])=8)

Measure 3. Sep>Aug = if(Sep'20>Aug'20,1,0)

Then you use the measure Sep>Aug in your conditional formatting and accordinlgy use the color. You may need to create lot of measures which is needed to create the type of visualization you wish to have.

Below how you can use conditional formatting after creating measures