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?
