Forum Discussion

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

The measures are not working as expected because in some DIM and Fact tables you have the key as text and in some it is a whole number.  These need to be the same.  Since you are not changing the calculation at different granularity you do not need INSCOPE().    You just need an iterator.  Also, because Region is a column in SlaesPerson you do not need to iteratrate over this level of the hierachy. You can use one measure for all visuals. 

 
#AverageAccuracyCorrected =
--get unique combo SalesPerson & Product for Sales
VAR _SalesKeys =
    SUMMARIZE(
        'FACT_SALES',
        'FACT_SALES'[SalesPersonKey],
        'FACT_SALES'[ProductGroupKey]
    )
 
--get unique combo SalesPerson & Product for Forecast
VAR _ForecastKeys =
    SUMMARIZE(
        'FACT_FORECAST',
        'FACT_FORECAST'[SalesPersonKey],
        'FACT_FORECAST'[ProductGroupKey]
    )
 
-- Combine them into one consolidated list of keys and removes duplicates.  This is just incase the salespeople in both tables don't match
VAR _AllKeys =
    DISTINCT( UNION( _SalesKeys, _ForecastKeys ) )
 
-- Map these raw keys to your the keys
VAR _VirtualGranularity =
    TREATAS(
        _AllKeys,
        'DIM_SALESPERSON'[SalesPersonKey],  -- Handles Region/SalesPerson hierarchy
        'DIM_PRODUCTGROUP'[ProductGroupKey] -- Handles the Product
    )
 
RETURN
-- Iterate over the Virtual Table
AVERAGEX(
    _VirtualGranularity,
    [#DivideAccuracy]
)
naelske_cronos's avatar
naelske_cronos
Icon for Advocate II rankAdvocate II
9 months ago

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