Forum Discussion

naelske_cronos's avatar
naelske_cronos
Icon for Advocate II rankAdvocate II
10 months ago
Solved

Handle 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...
"DAX " "DAX help" 'Need help'
  • naelske_cronos's avatar
    naelske_cronos
    10 months ago

    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 regards

     

    Kevin

naelske_cronos's avatar
naelske_cronos
Icon for Advocate II rankAdvocate II
10 months ago

Hello,

 

Thank you for your dedication on working on this particular problem and I see what you are trying to do. If I'm not mistaken you are calculating the accuracy of each row seperately and then doing an average.

For example for APR => George Jefferson => January I have 71.10% and you have 30.30%.

It is another way of calculating but the most important part is that the subtotals remains the same for each level in the hierarchy and that is what I am also trying to change. I still don't have the result as desired. I don't think it is possible to have some kind of hierarchy context filter on (sub)total level because this is where the dynamic change is important based on the current hierarchy.

 

A workaround is to work with seperate measures and bookmarks. It is an ugly way to workaround but I don't think there is another short term solution.

 

 

Thank you

Kind regards


Kevin

King7son1's avatar
King7son1
Frequent Visitor
10 months ago
#AverageAccuracyFinal =
VAR _VirtualTable =
    ADDCOLUMNS(
        DISTINCT(
            UNION(
                SUMMARIZE(
                    'fact_sales',
                    DIM_SALESPERSON[SalesPersonKey],
                    'DIM_DATE'[MonthKey] -- Ensure this matches your column header
                ),
                SUMMARIZE(
                    'fact_forecast',
                    DIM_SALESPERSON[SalesPersonKey],
                    'DIM_DATE'[MonthKey]
                )
            )
        ),
        "AtomAccuracy",
        VAR _Act = [#SumSales]
        VAR _Fcst = [#SumForecast]
        VAR _Denom = MAX(_Act, _Fcst)
        RETURN IF(_Denom > 0, 1 - DIVIDE(ABS(_Act - _Fcst), _Denom, 0), BLANK())
    )
 
VAR _WeightedMath =
    VAR _Act = [#SumSales]
    VAR _Fcst = [#SumForecast]
    VAR _Denom = MAX(_Act, _Fcst)
    RETURN IF(_Denom > 0, 1 - DIVIDE(ABS(_Act - _Fcst), _Denom, 0), BLANK())
 
RETURN

    IF(
        -- If we are on a Row Total (Month missing) OR Grand Total (Person missing)...
        NOT(ISINSCOPE(DIM_SALESPERSON[SalesPersonKey])) || NOT(ISINSCOPE('DIM_DATE'[MonthKey])),
       
        -- ...Then just Average the atoms (Fixes the Totals)
        AVERAGEX(_VirtualTable, [AtomAccuracy]),
       
        -- ...Else use Weighted Math (Keeps George at 71% inside the matrix)
        _WeightedMath
    )