Forum Discussion
naelske_cronos
Advocate II
Advocate IIHandle AverageX on different granularities matrix drilldown
Hello, I have a matrix in Power BI where you can drill down to different granularities, from region on top to customer level (3 levels). The problem is that when drilling down, my averageX is no...
Hello,
The question has not been resolved but I have found a workaround using bookmarks and seperate measures for each level of hierarchy. I guess the problem lies in the hierarchy context for the totals for which a single measure is "impossible" to use.
Kind regardsKevin
#AverageAccuracyCorrectedV1 =
-- Define the Virtual Table with hierachy and Accuracy
VAR _VirtualTable =
ADDCOLUMNS(
ADDCOLUMNS(
ADDCOLUMNS(
ADDCOLUMNS(
DISTINCT(
UNION(
SUMMARIZE(FACT_FORECAST,FACT_FORECAST[MonthKey],FACT_FORECAST[ProductGroupKey],FACT_FORECAST[SalesPersonKey]),
SUMMARIZE(FACT_SALES,FACT_SALES[MonthKey],FACT_Sales[ProductGroupKey],FACT_SALES[SalesPersonKey]))
),
"_Actual" , CALCULATE( SUM(FACT_SALES[Sales]), FACT_SALES[MonthKey] = EARLIER([MonthKey]) , FACT_SALES[ProductGroupKey] = EARLIER([ProductGroupKey]), FACT_SALES[SalesPersonKey] = EARLIER([SalesPersonKey] )) ,
"_Forecast" , CALCULATE( SUM(FACT_FORECAST[Forecast]), FACT_FORECAST[MonthKey] = EARLIER([MonthKey]) , FACT_FORECAST[ProductGroupKey] = EARLIER([ProductGroupKey]), FACT_FORECAST[SalesPersonKey] = EARLIER([SalesPersonKey] ))),
"_Diff" , ABS([_Actual] -[_Forecast]),
"_Denom" , MAX([_Actual], [_Forecast])),
"_Error" , DIVIDE ([_diff] , [_Denom],0)),
"Accuracy", 1 - [_Error])
RETURN
SWITCH(
TRUE(),
-- [CASE 1] Lowest Level: Product Group
-- If we are looking at a specific Product, calculate the simple math directly.
ISINSCOPE(DIM_PRODUCTGROUP[ProductGroupKey]) || ISINSCOPE(DIM_SALESPERSON[SalesPersonKey]),
[#DivideAccuracy],
-- [CASE 2] Totals & Subtotals (Default)
-- If we are on a SalesPerson Total, Region Total, or Grand Total,
-- calculate the AVERAGE of the rows in the Virtual Table.
AVERAGEX(_VirtualTable, [Accuracy])
)