Incorrect total: SUMX with ABS...

M

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,717
Hi All,

I'm struggling to get my head around the following and I hope someone can help.

Here's an example of what's happening:

[TABLE="width: 320"]
<tbody>[TR]
[TD="class: xl63, width: 64"]Product[/TD]
[TD="class: xl63, width: 64"]Forecast[/TD]
[TD="class: xl63, width: 64"]Sales[/TD]
[TD="class: xl63, width: 64"]Diff.[/TD]
[TD="class: xl63, width: 64"]ABS Diff.[/TD]
[/TR]
[TR]
[TD="class: xl63"]A[/TD]
[TD="class: xl63, align: right"]100[/TD]
[TD="class: xl63, align: right"]95[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl63"]B[/TD]
[TD="class: xl63, align: right"]150[/TD]
[TD="class: xl63, align: right"]135[/TD]
[TD="class: xl63, align: right"]15[/TD]
[TD="class: xl63, align: right"]15[/TD]
[/TR]
[TR]
[TD="class: xl63"]C[/TD]
[TD="class: xl63, align: right"]100[/TD]
[TD="class: xl63, align: right"]110[/TD]
[TD="class: xl63, align: right"]-10[/TD]
[TD="class: xl63, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl63"]D[/TD]
[TD="class: xl63, align: right"]50[/TD]
[TD="class: xl63, align: right"]75[/TD]
[TD="class: xl63, align: right"]-25[/TD]
[TD="class: xl63, align: right"]25[/TD]
[/TR]
[TR]
[TD="class: xl63"]E[/TD]
[TD="class: xl63, align: right"]50[/TD]
[TD="class: xl63, align: right"]10[/TD]
[TD="class: xl63, align: right"]40[/TD]
[TD="class: xl63, align: right"]40[/TD]
[/TR]
[TR]
[TD="class: xl63"]Total[/TD]
[TD="class: xl63, align: right"]450[/TD]
[TD="class: xl63, align: right"]425[/TD]
[TD="class: xl64, align: right"]25[/TD]
[TD="class: xl65, align: right"]25[/TD]
[/TR]
</tbody>[/TABLE]

I have created the following SUMX formula to calculate the ABS Diff., iterating through each row at a time:

Code: 
ABS Diff. = ABS(SUMX(Table,Table[Forecast]-Table[Sales]))

The row values returned are correct (in as much as they're the absolute difference between Forecast and Sales), but the total isn't correct and is a sum of the positive and negative values, i.e. it hasn't summed up the absolute values.

What have I done wrong?

Cheers,

Matty
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
Matty,

Did you copy the ABS formula all the way down to the Total Row for the ABS Column?
To get the value you are expecting you would want to just use the SUM Function. That would give you results of 25 and 95.
 
Upvote 0
frank_AL said:
Matty,

Did you copy the ABS formula all the way down to the Total Row for the ABS Column?
To get the value you are expecting you would want to just use the SUM Function. That would give you results of 25 and 95.
Click to expand...

Hi,

The value I need returning is 95, but the ABS(SUMX DAX formula I posted is giving 25.

To be clear: it's not a standard Excel formula I need, it's a DAX one for use in Power BI.

Thanks,

Matty
 
Upvote 0
You must create a measure: [ABS_Diff] and put it in ΣValues

Code: 
ABS_Diff:=SUMX(Table1, ABS(Table1[Forecast]-Table1[Sales]))
 
Upvote 0
citizenbh said:
You must create a measure: [ABS_Diff] and put it in ΣValues

Code: 
ABS_Diff:=SUMX(Table1, ABS(Table1[Forecast]-Table1[Sales]))
Click to expand...

Thanks - that works as desired.

The next problem I have is as follows...

The raw data is like this:

[TABLE="width: 356"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Product[/TD]
[TD]Month[/TD]
[TD]Forecast[/TD]
[TD]Sales[/TD]
[TD]ABS Diff.[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]A[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]A[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]135[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]A[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]B[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]B[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]A[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]A[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]B[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]B[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]B[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]220[/TD]
[TD="align: right"]375[/TD]
[TD="align: right"]155[/TD]
[/TR]
</tbody>[/TABLE]

And the above formula works as desired for calculating the ABS Diff. column. However, if I take Customer out of the table, the Product and Month values are summed, and I need the formula to calculate the ABS Diff. at that level, as shown below:

[TABLE="width: 320"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Product[/TD]
[TD="class: xl65, width: 64"]Month[/TD]
[TD="class: xl65, width: 64"]Forecast[/TD]
[TD="class: xl65, width: 64"]Sales[/TD]
[TD="class: xl65, width: 64"]ABS Diff.[/TD]
[/TR]
[TR]
[TD="class: xl65"]A[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl66, align: right"]150[/TD]
[TD="class: xl66, align: right"]65[/TD]
[TD="class: xl66, align: right"]85[/TD]
[/TR]
[TR]
[TD="class: xl65"]A[/TD]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl66, align: right"]175[/TD]
[TD="class: xl66, align: right"]200[/TD]
[TD="class: xl66, align: right"]25[/TD]
[/TR]
[TR]
[TD="class: xl65"]A[/TD]
[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl66, align: right"]100[/TD]
[TD="class: xl66, align: right"]110[/TD]
[TD="class: xl66, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl65"]B[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl66, align: right"]110[/TD]
[TD="class: xl66, align: right"]120[/TD]
[TD="class: xl66, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl65"]B[/TD]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl66, align: right"]130[/TD]
[TD="class: xl66, align: right"]95[/TD]
[TD="class: xl66, align: right"]35[/TD]
[/TR]
[TR]
[TD="class: xl65"]B[/TD]
[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl66, align: right"]220[/TD]
[TD="class: xl66, align: right"]375[/TD]
[TD="class: xl66, align: right"]155[/TD]
[/TR]
</tbody>[/TABLE]

In other words, depending on the aggregation done, the calculation needs to flex accordingly.

If anyone can give me a steer on this, it'd be much appreciated.

Cheers,

Matty
 
Upvote 0
All the same. You just need to put the PivotTable as follows:

In Section Rows: Product and Month
In Section ΣValues:Forecast, Sales and ABS_Diff
 
Upvote 0
citizenbh said:
All the same. You just need to put the PivotTable as follows:

In Section Rows: Product and Month
In Section ΣValues:Forecast, Sales and ABS_Diff
Click to expand...

Thanks for responding, but that isn't the case: it's actually summing up the row level absolute differences rather than grouping up the Forecast and Sales values first and then calculating the absolute difference (at the aggregated level).

Any ideas?

Thanks,

Matty
 
Upvote 0
citizenbh said:
Here are picture:

https://www.dropbox.com/home/MrExcel_Samples?preview=IncorectTotalABS.jpg
Click to expand...

Hi,

Thanks for your continued support.

I don't have a dropbox account, so I can't see what you posted. Also, I'm not using PowerPivot for this (I'm using Power BI), though I believe the DAX should be transferable.

Here's what I'm seeing (in Power BI):

Complete table:

[TABLE="width: 356"]
<tbody>[TR]
[TD="colspan: 3"]Customer + Product + Month[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer[/TD]
[TD]Product[/TD]
[TD]Month[/TD]
[TD]Forecast[/TD]
[TD]Sales[/TD]
[TD]ABS Diff.[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]A[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]A[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]135[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]A[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]B[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]B[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]A[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]A[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]B[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]B[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]B[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]220[/TD]
[TD="align: right"]375[/TD]
[TD="align: right"]155[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]885[/TD]
[TD][/TD]
[TD="align: right"]390[/TD]
[/TR]
</tbody>[/TABLE]

And your formula works fine.

But If I remove Customer, I get the following:

[TABLE="width: 320"]
<tbody>[TR]
[TD="colspan: 2"]Product + Month[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product[/TD]
[TD]Month[/TD]
[TD]Forecast[/TD]
[TD]Sales[/TD]
[TD]ABS Diff.[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]85[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]175[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]130[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]220[/TD]
[TD="align: right"]375[/TD]
[TD="align: right"]155[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]885[/TD]
[TD][/TD]
[TD="align: right"]320[/TD]
[/TR]
</tbody>[/TABLE]

Which is wrong.

Below is the same table with a column added to show what should be returned:

[TABLE="width: 389"]
<tbody>[TR]
[TD="colspan: 2"]Product + Month[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product[/TD]
[TD]Month[/TD]
[TD]Forecast[/TD]
[TD]Sales[/TD]
[TD]ABS Diff.[/TD]
[TD]Should Be[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]85[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]175[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]130[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]220[/TD]
[TD="align: right"]375[/TD]
[TD="align: right"]155[/TD]
[TD="align: right"]155[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]885[/TD]
[TD][/TD]
[TD="align: right"]390[/TD]
[TD="align: right"]320[/TD]
[/TR]
</tbody>[/TABLE]

Hope this helps explain the problem.

Cheers,

Matty
 
Last edited:
Upvote 0
I don't think you can create one measure that will work both with the customers included and without. The total row does not know what was is selected above (though I suppose you might use some sort of ISFILTERED().

What you need to do is iterate through the rows in your table with a SUMX. If your line items were defined by one column, ie just two lines for A and B it would be a simple VALUES ( Table[Product] ), but that won't work for two columns. I believe the "best practice" way to do this would be to use SUMMARIZE and ADDCOLUMNS (Brent Greenwood's blog: DAX Groupers: SUMMARIZE and AGGX(VALUES())), but I am not sure I know how to do that.

You could try instead.

Code: 
[Measure]:=
SUMX ( 
    CROSSJOIN ( 
        VALUES ( Table1[Product] ),
        VALUES ( Table1[Month] )
    ),
    CALCULATE ( ABS( Table1[Forecast]-Table1[Sales ] )
)
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
DAX: Double or Nested COUNTX() & SUMX()
Replies
6
Views
526
luuk
L
S
  • Question Question
DAX Expression Help
Replies
1
Views
152
RoryA
RoryA
U
  • Question Question
Power Query chose a value <=date
Replies
1
Views
201
AlienSx
A
RICH937
  • Question Question
How to create Running Totals on multiple non-date values and conduct filtered analysis on the results. Which Power Tool is the more efficient choice?
Replies
3
Views
436
RICH937
RICH937
E
  • Question Question
Percentage by Year instead of total
Replies
1
Views
246
EvonS
E

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top