cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Hammarberg
Frequent Visitor

How to calculate Forecast accuracy

Untitled.png

 

 

 

 

 

 

 

 

 

 

 

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 🙂 

 

15 REPLIES 15
Brutus82
Frequent Visitor

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

 

 

Greg_Deckler
Super User
Super User

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.

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.Untitled2.png

Untitled3.png

Sean
Community Champion
Community Champion

@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
Frequent Visitor

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.

 

 ForecastForecastSalesSales

 

 

 

Thanks for the help!

These links from SQLBI may be of some help:

 

http://www.daxpatterns.com/handling-different-granularities/

http://www.sqlbi.com/articles/budget-and-other-data-at-different-granularities-in-powerpivot/

 

There is also a good example on pp 378-381 of The Definitive Guide to DAX by Russo and Ferrari if you can get your hands on that book.

Thanks for the tip. Looked it through but I can't see anything that would help me. Most things are about measures, and from what I understand I should be using columns to be able to evaluate rows individualy.

 

Glad to receive any further insights or tips!

A measure can be evaluated row by row if your visualization is used correctly (matrix/table with the row identifiers as the rows).

 

 

Hi again,

 

Do you think you could elaborate based on the two sample pictures? I'm not sure how to get to the next step even though I've been trying to read through various postes as well as the links you provided. I'm rather new at DAX as you probably understand at this point 🙂

What I mean by a matrix/table using a measure as a column is like the following:

 

Capture.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

TYTDRunningUpTime and RDT Hours are both measures.  By putting Date and System as the rows, the measures essentially become columns in that visual.  So once you follow the instructions through the patterns linked about, you can use your new measures in any of your visuals.

Sean
Community Champion
Community Champion

@Hammarberg

If you want a MEASURE instead of COLUMN use this the SUMX function is an iterator

ABS 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 data

Post 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

Hello

what if the your data are not in the same table ? you can use the SUMX function

 

I got one table with forecasts per month per product

one table with sales per month per product

one quick measure to calculate per row the gap / delta

one measure to measure the accuracy at row level.

But i run into same issue to agregate at region or country level

i keep looking. if I found i will share with everyone. In the meantime, if you all ready have the solution, thanks

 

Anonymous
Not applicable

Hello

 

Did you find the solution ?

 

Best regards

 

Hammarberg
Frequent Visitor

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.

 

Example.xlsx

Sample.pbix

 

Let me know if there is any issues with the links (did not see how to upload files directly to the message).

When 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))

 

out.jpg

 

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)
)

Helpful resources

Announcements
Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Ignite 2022

What's Next at Microsoft Ignite 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Top Solution Authors