topic Re: VolPriceMix Calculation - how to multiply single rows by total/subtotal in the same table in DAX Commands and Tips

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

jordanesqu — Fri, 28 Aug 2020 13:27:58 GMT

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.

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

Anonymous — Fri, 28 Aug 2020 13:43:39 GMT

What does the model look like?

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

dedelman_clng — Fri, 28 Aug 2020 13:56:14 GMT

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

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

AntrikshSharma — Fri, 28 Aug 2020 14:26:56 GMT

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

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

amitchandak — Fri, 28 Aug 2020 15:28:49 GMT

jordanesqu , it can be

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

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/