Total forecast accuracy calculation using the aggregation of the absolute error at different level

[Anonymous]

Hello!

I hope someone can help me find out how to fix the issue below:

I need to calculate the forecast accuracy by different levels so I created sumamry tables by category, channel and month. 

The foreacst accuracy is calculated using the absolute error which is calculated at the lowest level, market-channel-product.

Actuals= Sales

Abs error= abs(actuals-forecast)

Forecast Accuracy= 1-(abs error/actuals)

PBI (also refer to screenshot)

• Category level: calculates forecast accuracy correctly
• Channel level: calculates foreacst accuracy correctly; but the total of the absolute error is not correct (218,389) PBI shows it as 180,519, same as in the category table.  
• Month YTD: calculates teh forecast accuracy incorrectly, absolute error is shown as 2,421,362 but when summing up the column, the correct total absolute error is 3,408,602.
• product level: calculates the forecast accuracy correctly

This screenshot shows the summary tables and the calculation in PBI, then table sin the middle are the same summary tables but I summed up the columns and calculated the forecast accuracy to validate the data.  

Tables on the right are summary tables from excel, which is what need power BI to do (data is not the same, is just the calculation example).

DAX formulas:

Fc Acc =

VAR _Error= DIVIDE([ABS Error],[Total Actuals])

RETURN IF(_Error > 1|| [Total Actuals]=0,0,(1-_Error))

ABS Error =

VAR _ABS= SUMMARIZE('ALL IP FILES','ALL IP FILES'[Description],"VALUE",[Formula 4Calc of ABS Error])

RETURN SUMX(_ABS,[VALUE])

Fcst= forecast

Formula 4Calc of ABS Error = ABS(SUM('ALL ACTUALS_PIPO'[Total Sales_ PIPO])-SUM('ALL IP FILES'[Ttl Fcst]))

[lbendlin · Accepted solution]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...