Forum Discussion
Absolute Value Forecast Accuracy Rollup/down
Hi. I am reaching out for help on how to properly calculate the absolute value ultimately at the item level but I would like the metric to continue to roll up/down through different categories. In the table below the Act vs Fcst (ABS) values are at the month level which is the absolute value of the Act vs Fcst column. This holds true at the total level where I would like to have the total be the sum of the months or level above. (Act Total). Any help would be greatly appreciated.
| Month | Act vs Fcst (ABS) | Act vs Fcst |
| January | 716,970 | (716,970) |
| February | 106,831 | (106,831) |
| March | 337,249 | (337,249) |
| April | 234,246 | (234,246) |
| May | 214,143 | (214,246) |
| June | 439,131 | (439,131) |
| July | 70,752 | 70,752 |
| PBI Total | 1,977,818 | (1,977,818) |
| Act Total | 2,119,322 |
You can do this by writing a measure that sums Act vs Fcst (ABS) at the month level.
Below is an example of this sort of measure:
(From your description, I'm assuming Act vs Fcst (ABS) is a column. If it's a measure, replace the code in red with the measure name.)
Act vs Fcst (ABS) summed by month = SUMX ( VALUES ( YourTable[Month] ), CALCULATE ( SUM ( YourTable[Act vs Fcst (ABS)] ) ) )Please try that out and post back if needed.
Regards,
Owen
4 Replies
You can do this by writing a measure that sums Act vs Fcst (ABS) at the month level.
Below is an example of this sort of measure:
(From your description, I'm assuming Act vs Fcst (ABS) is a column. If it's a measure, replace the code in red with the measure name.)
Act vs Fcst (ABS) summed by month = SUMX ( VALUES ( YourTable[Month] ), CALCULATE ( SUM ( YourTable[Act vs Fcst (ABS)] ) ) )Please try that out and post back if needed.
Regards,
Owen
Thank you for the quick response. I will give this a try. Just curious will this work when a user uses the drill through option?
Realised I hadn't responded to your follow-up question.
It should work with drill-through if you are displaying the same measure, but post back if you have a specific scenario where results are not as expected.
Regards,
Owen
Hi, I hope someone could help me.
I have a similar issue.
My database is vertical, so I have just one colunm for "Units" and other one with Type with two categories (Sales / Fcst).
how I can calculate the ABS variance with the right total?
thanks in advanced.
