Forum Discussion

Sk1_2's avatar
Sk1_2
Frequent Visitor
2 years ago

Calculate difference between multiple columns and then highlighting the difference

Hello I am trying to calculate the difference between mutiple columns in PowerBI. And Highlight changes above and below 50 w/ color. I haven't been able to find any examples with multiple colum...
Power
v-yanimei-msft's avatar
v-yanimei-msft
Community Support
2 years ago

Hi all , 

Anonymous , thanks for your concern about this case, I have some additions for problem solving.

Sk1_2 , thank you for giving a specific description of the problem. Based on your description, I will give you the following steps to solve the problem.

1.The test table Table.

2.New Measures and input the DAX code of calculating difference for individual columns.

 

Difference between Sum of 1 and Sum of 2 = SUM('Table'[Sum of 1])-SUM('Table'[Sum of 2])
Difference between Sum of 1 and Sum of 3 = SUM('Table'[Sum of 1])-SUM('Table'[Sum of 3])
Difference between Sum of 1 and Sum of 4 = SUM('Table'[Sum of 1])-SUM('Table'[Sum of 4])
Difference between Sum of 2 and Sum of 3 = SUM('Table'[Sum of 2])-SUM('Table'[Sum of 3])
Difference between Sum of 2 and Sum of 4 = SUM('Table'[Sum of 2])-SUM('Table'[Sum of 4])

 

3.Add these measures to columns.

4.Conditional formatting. Refer to Apply conditional table formatting in Power BI - Power BI | Microsoft Learn. All measures follow the steps:

a.Click the drop-down arrow, select Conditional formatting > Background color.

b.Select Rules as Format style.

c.Select Number or Percentage as value type.

d.Select the color you want to display.

You can modify them according to your needs.

5.The outcome is in the following picture.

 

 

Best Regards,

Caroline Mei

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

Sk1_2's avatar
Sk1_2
Frequent Visitor
2 years ago

Thank you! Can this formula work in a Matrix? 

  • v-yanimei-msft's avatar
    v-yanimei-msft
    Community Support
    2 years ago

    Sk1_2 , you can realize it in a Matrix. Now, I will show you how to realize it in a Matrix.

    1.Modify the test table Table. Add a column ValueType, so that you can use it as the row later.

    2.Add ValueType to Rows, and add Sum of 1, Sum of 2, Sum of 3, Sum of 4, Difference between Sum of 1 and Sum of 2, … to Values.

    3.The outcome is in the following picture.

    Best Regards,

    Caroline Mei

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