How to calculate Forecast accuracy

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

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

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.

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

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

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

Hello

Did you find the solution ?

Best regards