Hi, I've been trying to get my head around how to calculate a forecast accuracy. Here is how I usually set up a simply formula in excel: First I calculate the absolute variance between sales and forecast. I do this for each individual "Material" (product). absolute variance = ABS(Actual sales - forecast) Then I summerize the total ABS variance and the total forecast to get the Forecast Accuracy: Forecast Accuracy = 1 - ( Total ABS variance / Total Forecast ) (at the top you can find a small sample) If anyone has an idea it would be greatly appreciated. This is getting on my nerves :)

Pretty much the same. I created data with your firt 3 columns. Then a measure like this: Asolute Variance = ABS(SUM(ForecastAccuracy[Sold])-SUM(ForecastAccuracy[Forecast]))Then I creaed another measure like this: Forecast Accuracy = 1 - ( [Asolute Variance] / SUM([Forecast]) )Put the first 3 columns and the first measure into a table. Put the second measure into a card visualization. Your Forecast Accuracy will work in your table as well for the forecast accuracy of each material.

HammarbergIf you want a MEASURE instead of COLUMN use this the SUMX function is an iteratorABS Variance MEASURE = SUMX('Table', ABS('Table'[Forecast] - 'Table'[Sold]) ) Accuracy MEASURE = 1 - ( [ABS Variance MEASURE] / SUM('Table'[Forecast] ) )This works with the first example you posted! (You can filter the 'Table' in the SUMX if necessary...)To get anyone to help you further if this Measure doesn't help you with you actual dataPost sample data that can be copied and pasted into PBI not images!Otherwise you are asking people to spend the time to recreate your data in order to test it and many will skip over your question

Hi and thanks for the quick reply! I tried this with the sample and I get the Forecast Bias (95%) instead of Accuracy (90%). At Material level it would be correct, but it cannot handle the fact that some have sold above forecast and others below. So in short I think that the formula you provided does not evaluate each row, it summerizes them and then says to turnes it to "Absolute". Some how it needs to evaluate eatch Material first and then add every individual variance together.

Hammarbergconvert the abs variance to a columnABS Variance COLUMN = ABS ( 'Table'[Forecast] - 'Table'[Sold] ) Bias = DIVIDE ( SUM('Table'[Sold]), SUM('Table'[Forecast]), 0 ) Accuracy = 1 - (SUM('Table'[ABS Variance COLUMN]) / SUM('Table'[Forecast] ) )

Hi, I previously marked this post as solved. In relation to the sample I sent over it was. However after trying to use this solution on my actual data I noticed that I could not put the pieces together. One of my issues is that the actual data is divided into two separet sheets (which is how I retreive the information). See example pictures below. The actual sales is recorded per dayThe Forecast is recorded per week and "Scenario" ( Scenario + Date = forecast for that week )The forecast is recorded on the first day of the week I want to be able to calculate the forecast accuracy on different time periods (week/Month/Quarter/Year). But when I try Seans solution above, I think I'm getting the variance for each day. Can I create new tables with calculated columns for Week/Month etc and then summarize the variances in that table, or is there some other way of doing this? Please have a look at the example and see if you have better luck getting a grip on this. ForecastSales Thanks for the help!

How to calculate Forecast accuracy | Microsoft Fabric Community

15 Replies

Greg_Deckler
Community Champion
9 years ago
Pretty much the same. I created data with your firt 3 columns. Then a measure like this:

Asolute Variance = ABS(SUM(ForecastAccuracy[Sold])-SUM(ForecastAccuracy[Forecast]))
Then I creaed another measure like this:

Forecast Accuracy = 1 - ( [Asolute Variance] / SUM([Forecast]) )
Put the first 3 columns and the first measure into a table.

Put the second measure into a card visualization. Your Forecast Accuracy will work in your table as well for the forecast accuracy of each material.
- Hammarberg
  Frequent Visitor
  9 years ago
  Hi and thanks for the quick reply!
  
  I tried this with the sample and I get the Forecast Bias (95%) instead of Accuracy (90%). At Material level it would be correct, but it cannot handle the fact that some have sold above forecast and others below.
  
  So in short I think that the formula you provided does not evaluate each row, it summerizes them and then says to turnes it to "Absolute". Some how it needs to evaluate eatch Material first and then add every individual variance together.
  - Sean
    Community Champion
    9 years ago
    Hammarberg
    convert the abs variance to a column
    ABS Variance COLUMN = ABS ( 'Table'[Forecast] - 'Table'[Sold] ) Bias = DIVIDE ( SUM('Table'[Sold]), SUM('Table'[Forecast]), 0 ) Accuracy = 1 - (SUM('Table'[ABS Variance COLUMN]) / SUM('Table'[Forecast] ) )
Brutus82
Regular Visitor
8 years ago
As I see this problem, it's basically creating a Excel SUMIF() within DAX. My example is slightly different but hopefully illustrates the point. The key was creating a 'staticdate' dynamic value which every summed row was filtered on, like so:

Sum_error = VAR staticdate = nepool[date] return abs(sumx(FILTER(NEPOOL, NEPOOL[Date] = staticdate),NEPOOL[STF_Error])) / countrows(FILTER(NEPOOL, NEPOOL[Date] = staticdate))

Nepool is my dataset name. STF_Error is the error in the forecast. I divided it by the number of rows so the aggregation worked correctly.

James

Forum Discussion

How to calculate Forecast accuracy

15 Replies

Related content

Calculating Forecasting Accuracy

Forecast accuracy

Forecast Accuracy Analysis

Forecasting with DAX

Forecast Calculation