Forum Discussion
How to calculate Forecast accuracy
Hi Sean,
The solution you had for ONE table works perfectly. I've tried some different approaches but hit a dead end. Attached you find a bigger excel sample file and a pbix. If you would be ready to have a look it would be greatly appreciated.
Let me know if there is any issues with the links (did not see how to upload files directly to the message).
Resolver IWhen all values are in one table you can use the measures below. I couldn't figure out how to do it when each piece is in a different table. I tried SUMMARIZECOLUMNS but I couldn't get it to work...my attempt is at the bottom, maybe someone else can advise.
Measures for Same Table
Note: there was no "article" or 'date' column, and the join to 'Data in same model 2' confuses me. If you were to add Date and Article, you would probably want to summarize by those columns first.
Accuracy New =
VAR ABSErr =
SUMX(
'Data in same table 1',
CALCULATE (
ABS (
SUM ('Data in same table 1'[Forecast]) - SUM ('Data in same table 1'[Sold])
)
)
)
VAR Volume = SUM('Data in same table 1'[Sold])
VAR Accuracy = 1-(DIVIDE(AbsErr,Volume))
RETURN
IF(Volume=0,0,
IF(Accuracy<=0,0,
Accuracy)
)
Bias New =
VAR Volume = SUM('Data in same table 1'[Sold])
VAR Forecastvar = SUM('Data in same table 1'[Forecast])
RETURN
IF(Volume=0,IF(Forecastvar=0,0,1),(SUMX('Data in same table 1','Data in same table 1'[Forecast] - 'Data in same table 1'[Sold]) / Volume))
Measure for values in different tables Not Working
Forecast Accuracy =
VAR ABSErr =
SUMX(
SUMMARIZECOLUMNS(
'Dates'[Date],
'Articles'[Article]),
ABS (
SUM (Forecast[Forecast]) - SUM (Sales[Sold])
)
)
VAR Volume = SUM(Sales[Sold])
VAR Accuracy = 1-(DIVIDE(AbsErr,Volume))
RETURN
IF(Volume=0,0,
IF(Accuracy<=0,0,
Accuracy)
)