DAX

[Gathoni]

Hi all, just started using PowerBI in reporting and I need your help 🙂. 

I have sales and forecast by product, customer and week. 

I need to calculate forecast absolute deviation over any filter context such that deviation over customer can cancel out but over product deviation accumulates. 

Here is sample calculation

Product	Customer	Week	forecast	sales	deviation
A	X	1	20	18	2
A	X	2	18	20	-2
A	Y	1	33	27	5
A	Y	2	16	10	6
B	X	1	15	11	4
B	X	2	24	28	-4
B	Y	1	40	26	14
B	Y	2	24	22	2
Total					27

I need a measure 'absolute deviation' such as that the total/sum 'absolute deviation' is 31 instead of 27

and  'absolute deviation' over week is such that 

Week	absolute deviation
1	25
2	6
Total	31

and  'absolute deviation' over product and week is such that

product	Week	absolute deviation
A	1	7
A	2	4
B	1	18
B	2	-2
Total		31

Any leads on how I can write this measure is welcome.... 

Thanks 
Gathoni

[v-yinliw-msft · Accepted solution]

Hi @Gathoni ,

According to your description, I assume that you need the ABS, you can try this method:

Create a new table to calculate.

New table:

Table 2 =
SUMMARIZE (
    'Table',
    'Table'[Week],
    'Table'[Product],
    "absolute deviation", SUM ( 'Table'[deviation] )
)

In the table, add a new column:

ABS = ABS('Table 2'[absolute deviation])

Then in the Table, create three measures:

ABS = SUM('Table 2'[ABS])

NOTABS = SUM('Table 2'[absolute deviation])

Result =
IF ( HASONEVALUE ( 'Table 2'[absolute deviation] ), [NOTABS], [ABS] )

The result is :

For more information you can find in the .pbix  file.

Hope this helps you.

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[grantsamborn]

1. The 3rd line of your sample data shows [deviation] as 5 when it should be 6.
2. I can’t figure out what you mean by “absolute deviation” and its total being equal to 31.

https://1drv.ms/u/s!AnF6rI36HAVkhPF41NlaDnqg9IdTWA?e=N4ibe0