Forum Discussion
WMAPE/WAPE calculation with user-selected granularity
Hi MSpedaletti ,
If I understand correctly, the issue is that you want to calculate WMAPE. Please try the following methods and check if they can solve your problem:
1.Try to dynamically calculate the WAPE, create the measure that use the calculate function and the sumx function.
WAPE =
VAR TotalWeightedError =
SUMX(
'FactTable',
'FactTable'[Error] * RELATED('Dim_Ranking'[Weight])
)
VAR TotalWeightedActual =
SUMX(
'FactTable',
'FactTable'[Actual] * RELATED('Dim_Ranking'[Weight])
)
RETURN
DIVIDE(TotalWeightedError, TotalWeightedActual)
2.For the second question, calculation groups could be used to simplify the DAX measures. Calculation groups allow you to create a single set of calculations that can be reused across multiple measures.
Create calculation groups in Power BI (preview) - Power BI | Microsoft Learn
Best Regards,
Wisdom Wu
Hello, Wisdom Wu
Thank you for your reply.
I have tried the measure you suggested, but the issue persists.
The reason is that the error has to be calculated according to the selected granularity. For instance, if I have the following data:
| Family | Item | Forecast | Actual | Absolute error |
| A | Y | 10 | 8 | 2 |
| A | X | 5 | 9 | 4 |
If I apply the suggested measure, this is the result I get when looking at the Family granularity level:
| Family | Forecast | Actual | Absolute error |
| A | 15 | 17 | 6 |
The measure calculates for each Item and then aggregates as a sum for the Family.
When what I need is to evaluate the error directly on the Family level:
| Family | Forecast | Actual | Absolute error |
| A | 15 | 17 | 2 |
Is there a way (different then using calculated tables) to get the WAPE measure to work for different granularities?