I think ABS() should have to place before SUM()
SUM(ABS([Adjusted forecast DCH (kg)]-[Actual demand (kg)]))
Please try ...
Thanks for your advice. However, I have tried this out but this returns the number 5662. In the underlying data I have many rows for the same item (one row per customer). Maybe this has some effect to the result? However, I don’t want to sum up the deviations from the lowest level (customer). I want to sum up the absolute deviations on the values I have for the items (grouped, as in the picture). Any new idea?
Please, replace SUM by TOTAL. e.g. ABS(TOTAL([Adjusted forecast DCH (kg)]-[Actual demand (kg)])) and verify the results.
If you share a workbook with sample data it is easier for us to help you.
Thanks for your suggestion Ramon, but this didn’t work either. I’ve put together a simple workbook. See attachment. The question is basically, how do I get the “Absolute forecast error” to become “500” instead of “300” when removing “Item” from the rows.
Many thanks for the attention so far.
Example workbook.twb.zip 5.8 KB
Could you give the twbx file and not the tde file.
Please, export your workbook as package workbook and share it. As Chandra mention in the above post, we are not able to get the data source with a twb file.
Place Item on the level of detail shelf and create a calculated field similar to the following:
if (first()==0) then window_max([Absolute forecast error]) end
Place it on the view, right click on it and select Compute Using>Item.
You'll want to do the same thing for Actual demand and Adjusted forecast using a formula similar to:
if (first()==0) then window_sum(sum([Actual demand (kg)])) end
Hope this helps!