VolPriceMix Calculation - how to multiply single rows by total/subtotal in the same table

[jordanesqu]

Hi All,

I am trying to calculate price volume mix variance and having a trouble to find a way how to multiply the average value for wholeportfolio by values in a single rows.

I would like to multiply Delta Quantity for each product by average price per piece (yellow highlighted). I am aiming also the whole calculation work flexible as I have more dimensions like Region, Pack type, Customer etc.

Thank you for help.

[amitchandak · Accepted solution]

@jordanesqu , it can be

avg price per piece = calculate([price per piece], all(Table[product]))

or

avg price per piece = calculate([price per piece], all(Table))

new measure = [Delta Qty] * [avg price per piece]

also refer

https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept...

https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/

[dedelman_clng]

Hi @jordanesqu  - 

Try something like this:

VolPriceMix =
VAR __AllProdAvg =
    CALCULATE (
        DIVIDE ( SUM ( Products[Price] ), SUM ( Products[QtyCY] ) ),
        ALLSELECTED ( Products )
    )
RETURN
    CALCULATE ( SUM ( Products[DeltaQ] ) * __AllProdAvg )

Hope this helps

David

[AntrikshSharma]

@dedelman_clng  You can remove the last CALCULATE as it is not doing anything there 🙂

[Anonymous]

What does the model look like?