problem with absolute difference calculation

[Landraille]

Hello,

I have a table containing the stock of several items for 2 softwares.
I would like to compare the stock/item between the 2 softwares with an absolute difference.

I tried the measure

Difference abs sum = ABS(SUM('Table'[Quantity_Soft1])-SUM('Table'[Quantity_Soft2]))

The row values are good but the total is not good.
I tried the measure with sumx

Difference abs sumx = SUMX('Table',ABS('Table'[Quantity_Soft1]-'Table'[Quantity_Soft2]))

Not all the row values are good (why ?) and so, the total is not good too.

How i can do that ?

You can download the pbix here

Thank you

[Ashish_Mathur]

That link opens a sign-in page.

[Landraille]

Sorry, I forgot to change the rights. It's done

[Ashish_Mathur · Accepted solution]

Hi,

This measure works

Difference abs sumx = SUMX(SUMMARIZE('Table','Table'[Stock],'Table'[Item],"A",[Difference abs sum]),[A])

Furthermore, you may simplify your measure to

Difference abs sum = ABS([Difference])

Hope this helps.

[Landraille]

Thank you, the summarize function works in my case !

[Ashish_Mathur]

You are welcome.

[Sergii24]

Hi @Landraille, what you need to remember is that in the most cases when you write a formula in PowerBI, you refer to a column, not a row. 

Let's consider your measure:

Difference abs sum = ABS( SUM('Table'[Quantity_Soft1]) - SUM('Table'[Quantity_Soft2]) )

In this case you first sum the whole column "Quantity_Soft1" then sum "Quantity_Soft2", after that you deduct them and get an absolute value as a result. It looks correct, when you have only 1 row for each combination of Stock and Item (except Total row). When you arrive to the total, the same happens: PBI first get a sum of column 1 (considering the sign, so some values cancel each other), then of the column 2, deducts them and only then finds an absolute value.

Now the second one:

Difference abs sumx = 
   SUMX( 
      'Table',
      ABS( 'Table'[Quantity_Soft1] - 'Table'[Quantity_Soft2] )
   )

Here the sequence is the following: first we calculate a difference between value in Quantity_Soft1 and Quantity_Soft2 at row level (because you used a formula with X at the end). Then, you obtain the absolute value of the deducation (still at row level) and sum all of them (so only positive numbers are summed).

With this apporach, you would achieve the correct evalutation if Stock and Item would be the lowest level details in your table, which I expect is not (maybe you have date or anything else in the same table). Remember that deducation happens for every row of table "Table", which is filtered for, let's say "Stock2" and "Item1". Are you sure this table contains a signle row? If 10 and 6 are sum of more rows, the measure doesn't consider it: it performs operation row by row.

Here is the example when Stock and Item would be the only attrbutes of "Table" using the exact same measure.

You might consider use of Summarize() fucntion before applying the measure.

In case you need more help, please send a sample of your data which includes all columns of "Table" and the details of expected result (you can do it in Excel, for instance).

I hope my answer brings you some clarity 🙂 Enjoy your day!

[Landraille]

As you and Ashish Mathur said, the summarize function is the solution.
Thank you !