Forum Discussion

Hammarberg's avatar
Hammarberg
Frequent Visitor
9 years ago

How to calculate Forecast accuracy

                      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 calcula...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity 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's avatar
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's avatar
    Sean
    Icon for Community Champion rankCommunity 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] ) )

     

    • Hammarberg's avatar
      Hammarberg
      Frequent Visitor
      9 years ago

      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 day

      The 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!