Incorrect Totals in Power BI Table for MAPE

Question

I'm fairly new to Power BI and struggling with an issue around totals in a table.

I am trying to calculate Mean Average Percentage Error (MAPE) using the following calculation: [ABS(Actuals - Forecast)/Actuals]

Below is my dataset:

The total in the 'MAPEX' Column is actually the sum of the totals in 'AbsErr' / 'Actuals' columns: (1457.27 / 2786.27 = 0.52).

What I actually need to show is the sum of the values in 'MAPEX' which totals 5.88.

The 'MAPEX' column is a Measure with the following definition:

MAPEX = DIVIDE([AbsErr], sum(CUBE_PeriodicData[Actuals]),0)

I do not need to show the correct total in the 'Total' row in the table, it can be placed elsewhere in the report as a card, I would just like to know if there is a function in DAX that I am unaware of which will total the values in the column vertically?

The granularity matters. Do you always want to add up Absolute Percentage Error of each ItemName, Year, and Month? In that case, there is a simple solution. But if, for example, you may sometimes want to add up APE of each ItemName, Year, and Quarter, the result may not be the same. It would not be possible to switch the granularity used in the calculation depending on which drill-down level is selected in the visualization... — Kosuke Sakai
Yes, i always want to do this. The item and year will change, but the total should always reflect the 12 monthly values for the current selected Item in the chosen year. — B.M.

Kosuke Sakai Kosuke Sakai · Accepted Answer · 2020-01-24T02:33:11

Seymour's answer looks to be good, but I'm here to add a little that granularity matters in this scenario.

Assuming you have a star schema like this, it is pretty straightforward you can define measures Total Forecast, Total Actual, Absolute Error, and Absolute Percentage Error with below formulas.

Total Forecast            = SUM ( Forecast[Forecast] )
Total Actual              = SUM ( Actual[Actual] )
Absolute Error            = ABS ( [Total Forecast] - [Total Actual] )
Absolute Percentage Error = DIVIDE ( [Absolute Error], [Total Actual] )

Here is what you will get so far.

Here, you are asking how to calculate the sum of Absolute Percentage Errors.

By definition, Absolute Percentage Error shows the value of Absolute Error divided by Total Actual regardless of the drill-down level. Therefore at the grand total, it shows 0.52 which is Absolute Error (1,457.27) divided by Total Actual (2,786.27). If you want it to calculate differently in the grand total level, you need to explicitly implement this logic.

Your requirement would be stated more explicitly like below:

Calculate the values of Absolute Percentage Error in the granularity of each ItemName, Year, and Month.
And add them up.

The function you will need to implement this logic is SUMX. Also, you may explicitly use SUMMARIZE to make sure you are calculating Absolute Percentage Error in the specific granularity.

MAPEX = SUMX (
    SUMMARIZE (
        Forecast,
        'Product'[ItemName],
        'Calendar'[Year],
        'Calendar'[Month]
    ),
    [Absolute Percentage Error]
)

I have been emphasizing about the granularity so far. This is because if you are not conscious of the granularity, the result may look strange in some cases.

In the above image, MAPEX looks to be the same as Absolute Percentage Error except for the grand total. However, if you drill-down by Quarter instead of Month, you will notice it is not the same at all.

Absolute Percentage Error is showing the quotient of Absolute Error and Total Actual at quarterly level, whereas MAPEX is still summing up monthly values of Absolute Percentage Error, even though Month is not being displayed in the table.

So, my final word is, whenever you invent a new measure like MAPEX, you always need to ask yourself if it makes sense or not for every possible granularities.

Incorrect Totals in Power BI Table for MAPE

3 Answers