Forum Discussion

naelske_cronos's avatar
naelske_cronos
Icon for Advocate II rankAdvocate II
11 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
    11 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

King7son1's avatar
King7son1
Frequent Visitor
11 months ago
_DynamicMeasureAccuracy =
VAR HasRegion = ISINSCOPE( 'Region'[Region] )
VAR HasSalesPerson = ISINSCOPE( 'SalesPerson'[SalesPerson] )
 
RETURN
SWITCH( TRUE(),
 
    -- 1. DETAIL ROW (SalesPerson)
    -- If we are on a salesperson row, just run the standard accuracy math.
    HasSalesPerson,
    [_DivideAccuracy],
 
    -- 2. REGION ROW (The Fix)
     HasRegion,
    AVERAGEX(
        VALUES('SalesPerson'[SalesPerson]),
        [_DivideAccuracy]
    ),
 
    -- 3. GRAND TOTAL
    -- If we are at the total, Average the Regions (which are averages of salespeople).
    AVERAGEX(
        VALUES('Region'[Region]),
        CALCULATE(
            AVERAGEX(
                VALUES('SalesPerson'[SalesPerson]),
                [_DivideAccuracy]
            )
        )
    )
)