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,

 

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
10 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]
    )
)
  • naelske_cronos's avatar
    naelske_cronos
    Icon for Advocate II rankAdvocate II
    10 months ago

    Hello,

     

    It is indeed coming from two fact tables but I don't see where you are getting the averages for no salesperson?

     

    Your DAX query is giving me the correct values for SalesPerson which is a good start but not for other levels in my drilldown like the level above for customer region or if I want a level down to customer for example.

     

    For these 5 regions for January the average is 94.22% (Like doing a simple average in EXCEL)

    When I drill down to salesperson in my matrix for the first region APR the average should not be 89.61% but 85.84%

     

    I am using your measure:

    _DynamicMeasureAccuracy = 
VAR HasSalesperson = ISINSCOPE(DIM_Customer[CustomerSalesPersonDescr])
VAR HasMonth = ISINSCOPE(DIM_CalendarDate[MonthName])
 
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(DIM_Customer[CustomerSalesPersonDescr]),
        [_DivideAccuracy]
    ),
   
    // Row total - average across months for the current salesperson
    HasSalesperson && NOT HasMonth,
    AVERAGEX(
        VALUES(DIM_CalendarDate[MonthName]),
        [_DivideAccuracy]
    ),

   
    // Grand total - include combinations from both fact tables
    AVERAGEX(
        DISTINCT(
            UNION(
                SUMMARIZE(FACT_ActualsBudget, DIM_CalendarDate[MonthName], DIM_Customer[CustomerSalesPersonDescr]),
                SUMMARIZE(FACT_Forecast, DIM_CalendarDate[MonthName], DIM_Customer[CustomerSalesPersonDescr])
            )
        ),
        [_DivideAccuracy]
    )
)

    Which is good for sales person but not for the level up on regional level as it takes the values from sales person.

     

     

    I hope that this is more clear?

     

    Thanks

    Kind regards

     

    Kevin