NEED HELP with Calculate (Absolute percentage error)

[SIrigoyen95]

So, I need to calculate MAPE, which means I need to do (Forecast-Actual)/Forecast for each individual date and project (see the image for reference).

So, I need to have the (F-A)/F done for the Value column, where the Value is filtered for whether it's actual or Forecast, but for each individual project for each individual date. Of course, if I used CALCULATE, i could technically do a SUM, where in the filters i go one project and date a time, but that would take years. I'm looking for a solution and I just cannot come up with anything.

[v-xiaotang]

Hi @SIrigoyen95 

Thanks for reaching out to us.

I just want to confirm if you resolved this issue? If yes, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.

If you need more help, please let me know.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[Keith-Sayer]

It sounds like you are trying to do individual measures. This type of situation is probably better suited to a calculated column than a measure since you already have this split out in your data. In the data view, click "Add column" and enter it as DAX, something like:

MAPE = ('Table'[ForecastOnly] - 'Table'[Actuals])/'Table'[ForecastOnly]

It will calculate it per row, which I think is what you need given what you said in your request.

[SIrigoyen95]

I see what you mean, but if you see, if Actuals>0, then forecast=0, if Actual=0, forecast>0, because they are seperated since for each date you have an actual and a forecast. In the original source, there are two seperated columns for actuals in a October (for example) and forecast in october. So, yes, i could do a new query from the source where i apply that dax code and get a new column where the row is a product of that calculation. However, this limits me since I want to be able to filter by month, to be able to see what the MAPE was in previous months, do I explain myself correctly?

[Keith-Sayer · Accepted solution]

Okay, I think I have a better idea of what you are asking for. So you would like to have it run this calculate for whatever time range you have selected and then for whatever project you have selected?

If so, I would skip the calculate function and just do something in a measure like:

MAPE = (sum(actuals)-sum(forecast))/sum(forecast)

Then apply your different filters on the report page with slicers.

Or you could use Project name or date on charts as well to separate them. It should recalculate the measure for each chunk.