# Incorrect total: SUMX with ABS...



## Matty (Jun 14, 2017)

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:


ProductForecastSalesDiff.ABS Diff.A1009555B1501351515C100110-1010D5075-2525E50104040Total4504252525

<tbody>

</tbody>
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


----------



## frank_AL (Jun 14, 2017)

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.


----------



## Matty (Jun 14, 2017)

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.



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


----------



## citizenbh (Jun 14, 2017)

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


```
ABS_Diff:=SUMX(Table1, ABS(Table1[Forecast]-Table1[Sales]))
```


----------



## Matty (Jun 14, 2017)

citizenbh said:


> You must create a measure: [ABS_Diff] and put it in ΣValues
> 
> 
> ```
> ...



Thanks - that works as desired.

The next problem I have is as follows...

The raw data is like this:


CustomerProductMonthForecastSalesABS Diff.1A110055451A2150135151A3100110101B15075251B25010402A15010402A22565402B16045152B2808552B3220375155

<tbody>

</tbody>
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:


ProductMonthForecastSalesABS Diff.A11506585A217520025A310011010B111012010B21309535B3220375155

<tbody>

</tbody>
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


----------



## citizenbh (Jun 14, 2017)

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


----------



## Matty (Jun 14, 2017)

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



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


----------



## citizenbh (Jun 14, 2017)

Here are picture:

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


----------



## Matty (Jun 15, 2017)

citizenbh said:


> Here are picture:
> 
> https://www.dropbox.com/home/MrExcel_Samples?preview=IncorectTotalABS.jpg



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:


Customer + Product + MonthCustomerProductMonthForecastSalesABS Diff.1A110055451A2150135151A3100110101B15075251B25010402A15010402A22565402B16045152B2808552B3220375155885390

<tbody>

</tbody>
And your formula works fine.

But If I remove Customer, I get the following:


Product + MonthProductMonthForecastSalesABS Diff.A11506585A217520025A310011010B111012010B21309535B3220375155885320

<tbody>

</tbody>
Which is wrong.

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


Product + MonthProductMonthForecastSalesABS Diff.Should BeA1150658585A21752005525A31001101010B11101204010B2130954535B3220375155155885390320

<tbody>

</tbody>
Hope this helps explain the problem.

Cheers,

Matty


----------



## gazpage (Jun 15, 2017)

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 ] )
)
```


----------



## Matty (Jun 14, 2017)

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:


ProductForecastSalesDiff.ABS Diff.A1009555B1501351515C100110-1010D5075-2525E50104040Total4504252525

<tbody>

</tbody>
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 (Jun 15, 2017)

gazpage said:


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



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 (Jun 15, 2017)

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.


----------

