Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
harirao
Post Prodigy
Post Prodigy

Conditional formatting using Row Level comparing with column Header

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

2_2.PNG

2_1.PNG

 

Thanks in advance for any help on the topic!

2 ACCEPTED SOLUTIONS
MFelix
Super User
Super User

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.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

negi007
Community Champion
Community Champion

@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

 

dax copy.png




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos



Proud to be a Super User!


Follow me on linkedin

View solution in original post

2 REPLIES 2
negi007
Community Champion
Community Champion

@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

 

dax copy.png




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos



Proud to be a Super User!


Follow me on linkedin

MFelix
Super User
Super User

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.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.