topic Re: Calculate difference between multiple columns and then highlighting the difference in DAX Commands and Tips

Calculate difference between multiple columns and then highlighting the difference

Sk1_2 — Fri, 22 Mar 2024 15:48:49 GMT

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 columns. I believe I have to create a measure but I'm not sure what calcualtions to use. Any help would be much appreciated.

Re: Calculate difference between multiple columns and then highlighting the difference

Anonymous — Fri, 22 Mar 2024 18:36:30 GMT

Hello,

if you want the differences between two columns, you can do this : measure = SUM(Sum of 1) - SUM(SUM of 2).. etc
if you want the differences in % to do a highlight when the differences is > than 50%, you should do something like this, and use the pourcentage as a "cell elements" :

Pourcentage = DIVIDE(SUM('Table (2)'[Value 2]) - SUM('Table (2)'[Value 1]),SUM('Table (2)'[Value 1]) )

If your columns are something like a date, or months, you should think about do a pivot to get thoses columns in rows. And then use DAX date functions to compare thoses dates.

Re: Calculate difference between multiple columns and then highlighting the difference

v-yanimei-msft — Tue, 26 Mar 2024 05:08:52 GMT

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.

Re: Calculate difference between multiple columns and then highlighting the difference

Sk1_2 — Tue, 26 Mar 2024 17:22:08 GMT

Thank you! Can this formula work in a Matrix?

Re: Calculate difference between multiple columns and then highlighting the difference

v-yanimei-msft — Wed, 27 Mar 2024 02:13:59 GMT

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.}