Handle AverageX on different granularities matrix drilldown

Hello amitchandak,

I did like you told me too. This is my code:

_DynamicMeasureAccuracy2 =
VAR values_columns =
    VALUES ( DIM_CalendarDate[MonthName] )
RETURN
    SWITCH (
        TRUE (),
        ISINSCOPE ( DIM_CalendarDate[MonthName] ), [_DivideAccuracy],
        ISINSCOPE ( DIM_Customer[CustomerRegion] ), AVERAGEX ( values_columns, [_DivideAccuracy] ),
        ISINSCOPE ( DIM_Customer[CustomerSalesPersonDescr] ),
            AVERAGEX (
                CROSSJOIN ( VALUES ( DIM_Customer[CustomerRegion] ), values_columns ),
                [_DivideAccuracy]
            ),
        AVERAGEX (
            CROSSJOIN ( VALUES ( DIM_Customer[CustomerSalesPersonDescr] ), values_columns ),
            [_DivideAccuracy]
        )
    )

As you can see, the grand total for region is not correct because it is it 78.52% instead of 91.59%. That is because it checks the last condition of the switch for sales person:

On sales person the grand total is correct but the subtotals are not correct as for january it should be 85.84%

I guess it is a good base with the ISINSCOPE but how do my subtotals change based on the filter context if region or if salesperson => region is 1 row so for january 89.61% / 1  = 89.61% but when on sales person (74.02% + 96.23% + 75.06% + 100.00% + 78.94% + 96.65% + 80.00%) / 7 = 85.84%

Thanks

Kind regards

Kevin

Hello,

Well I tried this DAX code on my example that I showed instead of the complex one:

ExcelStyle = 
VAR RowIsDetail   = ISINSCOPE(SalesPerson[Key])
VAR region   = ISINSCOPE(Region[Region])
VAR ColIsDetail   = ISINSCOPE('Date'[Month])
RETURN

SWITCH(
    TRUE(),

    -- DETAIL CELL: salesperson + month → show sum
    ColIsDetail && (RowIsDetail || region),
        [SumValue],

    -- ROW SUBTOTAL (region) → show average
    NOT RowIsDetail && ColIsDetail,
        average('FACT'[Value]),

    -- COLUMN TOTAL (month total row) → show average
    RowIsDetail && NOT ColIsDetail,
         average('FACT'[Value]),

    -- GRAND TOTAL (neither in scope) → show average
     average('FACT'[Value])
)

but the problem is that the total does not know when to use regional level or sales person level because there is no filter context on total row, so still no luck...

Kind regards

Kevin

Please let us know if this solution works.  Also, for a more performant solution try the following.  I was not able to save the file in GIT as I did not have permissions 

Hello,

The keys were indeed in text format and I changed them but it didn't change anything to the measures. I did the calculations in EXCEL and the averages seems correct to me.

I have added additional matrices with your formulas but the values don't seem to be the same with one formula, unfortunately. You can find it attached in the Github link I provided you earlier.

Mine is INITIAL and yours is CORRECTED. When doing the math:

• January = (16,91% + 71,10% + 91,25% + 73,96% + 9,11%) / 5 = 52.47% (as blanks are ignored)
• APR = Abraham Lincoln =  (16,91% + 92.04%) / 2 = 54.48% (as blanks are ignored)

I don't know why your formula shows a different output.

Thanks

Kind regards

Kevin