Incorrect total: SUMX with ABS...

[Matty]

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:

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

 

[Matty]

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.

[Measure]:=
SUMX ( 
    CROSSJOIN ( 
        VALUES ( Table1[Product] ),
        VALUES ( Table1[Month] )
    ),
    CALCULATE ( ABS( Table1[Forecast]-Table1[Sales ] )
)

Hi Gaz,

Thanks for that - it works as desired.

Could you help me to understand what's going on here? I know that the VALUES function returns a distinct list of values, but I'm not clear as to what the CROSSJOIN is doing?

I'd already come across Brent Greenwood's blog, so it's good that I was on the right tracks. DAX is still relatively new to me though, so it's taking me time to get my head around it. One thing that I do miss with DAX compared to standard Excel formulas is the 'Evaluate Formula' window in Excel, as you can see the intermediate steps the formula is doing to compute the final result, which helps understanding. Something like this in DAX would be really useful!

Thanks again for your help!

Matty

 

[gazpage]

As I say, I don't think my approach is best practice but glad it works.

Basically you need to iterate through the rows in your example table, do the ABS calc for each row, and then sum up the result. This is basically the definition of SUMX. However, you don't want to SUMX on the actual table or it will go row by row down the source table and not at the aggregated level.

We could try using VALUES ( Table[Product] ) in the table part of the SUMX, but then that would just do the calculation for once for Product A and once for Product B, and add the two results. We actually want to go through the table like A1, A2, A3, B1, B2, B3 and then SUM the results. Cross Join takes two tables A, B and 1, 2, 3 and creates the Cartesian product, ie returns all combinations multiplied together. This is the desired A1, A2, A3, B1, B2, B3.