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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
william0076
Frequent Visitor

How do I do a conditional format to compare difference vs another column

Hi There,

 

I'm attempting to do a conditional formatting with icons to see whether there has been in improvement in 2021 vs 2020.

The values are on the rows and I'm trying to a conditional format where if

 

2021 value below 2020 by 0% = Red

2021 Value between 0-5% of 2020 = Yellow

2021 Value above 2020 by 5%+ = Green

 

william0076_1-1609845661576.png

I have assumed the conditional format as below. I've made a % change vs LY DAX to help calculate the formatting but this also falls into the 2020 number. Is there a calculation i need to perform to enable this conditional format?

william0076_2-1609845935059.png

Thanks

 

 

1 ACCEPTED SOLUTION
v-robertq-msft
Community Support
Community Support

Hi, @william0076 

According to your description, you said that OPFC 2021 and 2020 are in the same column in your dataset. I think you can get the same output using measure and Matrix in Power BI. You can try my steps:

This is my test data based on your description:

v-robertq-msft_0-1610332209728.png

  1. I created these measures:
2020 OPFC =

CALCULATE(SUM('Table'[OPFC]),FILTER('Table',YEAR([Date])=2020))
2021 OPFC =

CALCULATE(SUM('Table'[OPFC]),FILTER('Table',YEAR([Date])=2021))
Color =

var _diff=[2021 OPFC]-[2020 OPFC]

var _diffpercent=DIVIDE(_diff,[2020 OPFC])

return

SWITCH(

    TRUE(),

    _diffpercent<0,"Red",

    _diffpercent>=0&&_diffpercent<=0.05,"Yellow",

_diffpercent>0.05,"Green")
  1. I created a Matrix and place columns and measures then set the conditional format like this:

v-robertq-msft_1-1610332209747.png

 

v-robertq-msft_2-1610332209754.png

 

And you can get what you want.

You can download my test pbix file here

 

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

 

Best Regards,

Community Support Team _Robert Qin

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

View solution in original post

4 REPLIES 4
v-robertq-msft
Community Support
Community Support

Hi, @william0076 

According to your description, you said that OPFC 2021 and 2020 are in the same column in your dataset. I think you can get the same output using measure and Matrix in Power BI. You can try my steps:

This is my test data based on your description:

v-robertq-msft_0-1610332209728.png

  1. I created these measures:
2020 OPFC =

CALCULATE(SUM('Table'[OPFC]),FILTER('Table',YEAR([Date])=2020))
2021 OPFC =

CALCULATE(SUM('Table'[OPFC]),FILTER('Table',YEAR([Date])=2021))
Color =

var _diff=[2021 OPFC]-[2020 OPFC]

var _diffpercent=DIVIDE(_diff,[2020 OPFC])

return

SWITCH(

    TRUE(),

    _diffpercent<0,"Red",

    _diffpercent>=0&&_diffpercent<=0.05,"Yellow",

_diffpercent>0.05,"Green")
  1. I created a Matrix and place columns and measures then set the conditional format like this:

v-robertq-msft_1-1610332209747.png

 

v-robertq-msft_2-1610332209754.png

 

And you can get what you want.

You can download my test pbix file here

 

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

 

Best Regards,

Community Support Team _Robert Qin

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

v-robertq-msft
Community Support
Community Support

Hi, @william0076 

According to your description and sample picture, I can understand what you want to get. I suggest you to to use the measure to set the conditional formatting, you can try my steps:

This is my sample data created based on your picture:

v-robertq-msft_0-1610008689585.png

  1. I created a measure:
Color =

var _diff=MAX('Table'[2021 OPFC])-MAX('Table'[2020 OPFC])

var _diffpercent=DIVIDE(_diff,MAX('Table'[2020 OPFC]))

return

SWITCH(

    TRUE(),

    _diffpercent<0,"Red",

    _diffpercent>=0&&_diffpercent<=0.05,"Yellow",

_diffpercent>0.05,"Green")

 

  1. I created a table and place the three columns and measure, then I set the conditional format like this:

v-robertq-msft_1-1610008689599.png

 

  1. And you can get what you want, like this:

v-robertq-msft_2-1610008689607.png

 

You can download my test pbix file here

 

Best Regards,

Community Support Team _Robert Qin

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

Hey Mate,

 

Thanks for the advice here.

 

My Date numbers were collated together within the table format (The data comes from a CSV file which collates all the dates in meaning they cannot be split into columns) so i wouldn't be able to split it unless i split the data out at the very beginning. 

 

Is there another way to do the comparison if the data has the be together (OPFC 2021 and 2020 are in the same column)

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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