Calculating % Volume Change via Outer Filter Total (ALLSELECTED + SUMX)

[Anonymous]

Hi,

I'm trying to to calculate the Revenue Impact due to the change in volume mix.

I'm using two different functions in my measure to do so:

1. SUMX => This is to SUM the total impact of each rows result in the table
2. ALLSELECTED => This is to ensure the denominator is adjusted based on the slicers selected for the outer filter

Ideally, the base (denominator) used to calculate the volume should be flexible based on the slicers set by the outer fiter.  Ie. If No slicers are selected => the total volume should be the base, if Product 1 is selected => the total product 1 volume should be the base, or if Product 1 & May + June is selected => Total product 1 volume in May and Jun should be the base.

I know the use of SUMX + ALLSELECTED does yield a different result via the shadow filters.  That said, given the above is there a way to achieve this result?  I would ideally like one measure that can calculate the impact (via adjusting the denominator) based on what slicers are selected.

I've shared two examples in the dropbox one with ALLSELECTED and ALL.  As seen in the example ALLSELECTED's  output should match ALL given no slicers are selected.

https://www.dropbox.com/sh/3q4iir5qcjx59gv/AADYjeOvYJnsCzohWXckvX6ra?dl=0  

Thanks,
Alex 

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

I'd like to suggest you modify 'Vol Mix Imapct (ALLSELECTED)' measure as below. You may use a variable to keep current value of the 'CALCULATE' expression.

Vol Mix Impact (ALLSELECTED) = 
var _y2unit = 
CALCULATE(
    SUM('For forum'[Y2 Units]),
    ALLSELECTED('For forum')
)
var _y1unit = 
CALCULATE(
    SUM('For forum'[Y1 Units]),
    ALLSELECTED('For forum')
)
return
SUMX(
    'For forum',
    (
        (
            'For forum'[Y2 Units]/ 
            _y2unit
        )
    - (
        'For forum'[Y1 Units]/ 
        _y1unit
      )
    )
    *'For forum'[Y1 Revenue/Unit]
    * _y2unit
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.