Forum Discussion

william0076's avatar
william0076
Frequent Visitor
5 years ago
Solved

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...
Need Help
Tips and Tricks
  • v-robertq-msft's avatar
    v-robertq-msft
    5 years ago

    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:

    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:

     

     

    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.