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
    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

As Amit said you can use INSCOPE to perform different calculations

 

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 your base measure

    HasSalesPerson,

    [_DivideAccuracy],

 

    -- 2. REGION ROW (The Fix)

    -- If we are on a Region row, we can’t just use base measure

    -- Instead, iterate through the Salespeople and average their individual scores

    HasRegion,

    AVERAGEX(

        VALUES('SalesPerson'[SalesPerson]),

        [_DivideAccuracy]

    ),

 

    -- 3. GRAND TOTAL

    -- If we are at the total, Average the Regions (which are averages of salespeople).

    -- Or change this if you want average across every salesperson ignoring region ( AVERAGEX(VALUES(‘SalesPerson’[SalesPerson]), [_DivideAccuracy])     ….)

           

    AVERAGEX(

        VALUES('Region'[Region]),

        CALCULATE(

            AVERAGEX(

                VALUES('SalesPerson'[SalesPerson]),

                [_DivideAccuracy]

            )

        )

    )

)

 

King7son1's avatar
King7son1
Frequent Visitor
9 months ago

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 

#AverageAccuracyCorrected =
--[1]Instead of DISTINCT(UNION pattern create gain by grouping dimension columns.  This has a significant performance boost vs scanning the fact tables
-- Because we are using Dimension columns this virtual table should maintain lineage to the Dimensions.
VAR _Grain =
    DISTINCT(
        UNION(
            -- 1. Get keys from Forecast,
            SUMMARIZE(
                FACT_FORECAST,
                DIM_DATE[MonthKey],            
                DIM_PRODUCTGROUP[ProductGroupKey],
                DIM_SALESPERSON[SalesPersonKey]
            ),
            -- 2. Get keys from Sales,
            SUMMARIZE(
                FACT_SALES,
                DIM_DATE[MonthKey],
                DIM_PRODUCTGROUP[ProductGroupKey],
                DIM_SALESPERSON[SalesPersonKey]
            )
        )
    )
 
-- [2] now add the facts for sales and forecast
VAR _TableWithMetrics =
    ADDCOLUMNS(
        _Grain,
        "@Actual", [#SumSales],       -- Measure with CALCULATE(SUM(...))
        "@Forecast", [#SumForecast]   -- Measure with CALCULATE(SUM(...))
    )

-- [3] Add the Math (Accuracy logic)
-- We create a new variable so we can reference [@Actual] and [@Forecast] created in the previous step
VAR _TableWithAccuraryLogic =
    ADDCOLUMNS(
        _TableWithMetrics,
        "@Diff", ABS( [@Actual] - [@Forecast] ),
        "@Denom", MAX( [@Actual], [@Forecast] ),
        "@AccuracyRaw",
            VAR _Diff = ABS( [@Actual] - [@Forecast] )
            VAR _Denom = MAX( [@Actual], [@Forecast] )
            RETURN
            IF( _Denom = 0, 0, 1 - DIVIDE( _Diff, _Denom ) )
    )
RETURN
    SWITCH(
        TRUE(),
 
        -- [CASE 1] Lowest Level: Product Group or salesperson
        -- If we are looking at a specific SalesPerson or Product complete the accuaracy math
        ISINSCOPE(DIM_PRODUCTGROUP[ProductGroupKey]) || ISINSCOPE(DIM_SALESPERSON[SalesPersonKey]),
        [#DivideAccuracy],
 
        -- [CASE 2] Totals & Subtotals (Default)
        -- If we are on a SalesPerson Total, Region Total, or Grand Total,
        -- calculate the AVERAGE of the rows in the Virtual Table.
        AVERAGEX(_TableWithAccuraryLogic, [@AccuracyRaw])
    )
  • naelske_cronos's avatar
    naelske_cronos
    Icon for Advocate II rankAdvocate II
    9 months ago

    Hello,

     

    Thank you for your dedication on working on this particular problem and I see what you are trying to do. If I'm not mistaken you are calculating the accuracy of each row seperately and then doing an average.

    For example for APR => George Jefferson => January I have 71.10% and you have 30.30%.

    It is another way of calculating but the most important part is that the subtotals remains the same for each level in the hierarchy and that is what I am also trying to change. I still don't have the result as desired. I don't think it is possible to have some kind of hierarchy context filter on (sub)total level because this is where the dynamic change is important based on the current hierarchy.

     

    A workaround is to work with seperate measures and bookmarks. It is an ugly way to workaround but I don't think there is another short term solution.

     

     

    Thank you

    Kind regards


    Kevin

    • King7son1's avatar
      King7son1
      Frequent Visitor
      9 months ago
      #AverageAccuracyFinal =
      VAR _VirtualTable =
          ADDCOLUMNS(
              DISTINCT(
                  UNION(
                      SUMMARIZE(
                          'fact_sales',
                          DIM_SALESPERSON[SalesPersonKey],
                          'DIM_DATE'[MonthKey] -- Ensure this matches your column header
                      ),
                      SUMMARIZE(
                          'fact_forecast',
                          DIM_SALESPERSON[SalesPersonKey],
                          'DIM_DATE'[MonthKey]
                      )
                  )
              ),
              "AtomAccuracy",
              VAR _Act = [#SumSales]
              VAR _Fcst = [#SumForecast]
              VAR _Denom = MAX(_Act, _Fcst)
              RETURN IF(_Denom > 0, 1 - DIVIDE(ABS(_Act - _Fcst), _Denom, 0), BLANK())
          )
       
      VAR _WeightedMath =
          VAR _Act = [#SumSales]
          VAR _Fcst = [#SumForecast]
          VAR _Denom = MAX(_Act, _Fcst)
          RETURN IF(_Denom > 0, 1 - DIVIDE(ABS(_Act - _Fcst), _Denom, 0), BLANK())
       
      RETURN

          IF(
              -- If we are on a Row Total (Month missing) OR Grand Total (Person missing)...
              NOT(ISINSCOPE(DIM_SALESPERSON[SalesPersonKey])) || NOT(ISINSCOPE('DIM_DATE'[MonthKey])),
             
              -- ...Then just Average the atoms (Fixes the Totals)
              AVERAGEX(_VirtualTable, [AtomAccuracy]),
             
              -- ...Else use Weighted Math (Keeps George at 71% inside the matrix)
              _WeightedMath
          )