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
    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
11 months ago

Hello,

 

I made an example to make it more clear what I want because I don't know how to solve it with your reply. 

 

This is my example with the same formula I used. These are 3 dimensions linked to a fact (not following correct naming and best practices but it still works).

On regional level the grand total should be 25 as => (28 jan + 22 feb) / 2 = 25

On sales person level the grand total is correct which is 6.5 but the subtotal per month is not correct and should be 7 for january and 6 for febuary which makes 6.5 for the grand total.

_DynamicMeasureAccuracy2 = 
VAR values_columns =
    VALUES ( 'Date'[Month] )
RETURN
    SWITCH (
        TRUE (),
        ISINSCOPE ( 'Date'[Month] ), [SumValue],
        ISINSCOPE ( Region[Region] ), AVERAGEX ( values_columns, [SumValue] ),
        ISINSCOPE (  SalesPerson[SalesPerson]),
            AVERAGEX (
                CROSSJOIN ( VALUES ( Region[Region] ), values_columns ),
                [SumValue]
            ),
        AVERAGEX (
            CROSSJOIN ( VALUES ( SalesPerson[SalesPerson] ), values_columns ),
            [SumValue]
        )
    )

 

 

Thanks

Kind regards

 

Kevin

 

King7son1's avatar
King7son1
Frequent Visitor
11 months ago

It appears that you have two fact tables (Actual, Forecast).  Please note your _DivideAccuracy creates rows averages with no salesperson.  I am not sure what you are trying to do there.     Are you trying to filter out rows where you do not have both forecast and actuals for the given date?  In any case try this measure:

_DynamicMeasureAccuracy =


VAR HasSalesperson = ISINSCOPE(SalesPerson[SalesPersonKey])
VAR HasMonth = ISINSCOPE('Date'[Month])
 
RETURN
SWITCH(
    TRUE(),
    // Detail cell - show actual value
    HasSalesperson && HasMonth,
    [_DivideAccuracy],
   
    // Column total - average across salespersons for the current month
    HasMonth && NOT HasSalesperson,
    AVERAGEX(
        VALUES(SalesPerson[SalesPersonKey]),
        [_DivideAccuracy]
    ),
   
    // Row total - average across months for the current salesperson
    HasSalesperson && NOT HasMonth,
    AVERAGEX(
        VALUES('Date'[Month]),
        [_DivideAccuracy]
    ),
   
    // Grand total - include combinations from both fact tables
    AVERAGEX(
        DISTINCT(
            UNION(
                SUMMARIZE(fForecast, 'Date'[Month], SalesPerson[SalesPersonKey]),
                SUMMARIZE(fActual, 'Date'[Month], SalesPerson[SalesPersonKey])
            )
        ),
        [_DivideAccuracy]
    )
)