I think it is a very common issue.
I’m trying to do an absolute forecast error calculation by doing ABS(Forecast – Demand)
at the row level it is working fine, but when I am trying to use the aggregated value it is not doing the aggregate as I would expect it.
Also based on adding or removing granularity aggregation are changing but again all things are working fine only at the row level.

For example in the image, my SQL expression working fine at the lowest granularity but if I remove the City code, I’ll still have the total absolute error of 26204 instead of 12031.

Tools I have access to is SSMS and SAP SAC Story Dashboard, and none are able to give the desired out. I have tried multiple ways but to no avail.

OR it is not at all possible the I’m expecting and it needs to be separate data tables/views as per my granularity?