Forum Discussion
DAX
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
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.
2 Replies
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
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.