Dax for price volume mix effect calculation

[Anonymous]

Hi

I need to implement an originally Excel-based price-volume-mix analysis on power bi and have encountered some problems when trying to work out the total mix effect.

The original Excel formulas for volume,price and mix effect are as follows:

Volume effect=(volume actual-volume budget)*budget price-Mix Effect

Mix effect= total volume actual * (sales mix actual-sales mix budget)*(price budget-average budget price)

Price effect= (price actual-price budget)*actual volume

The Dax for the 3 effects are as follows:

Price effect:  sumx(values('Product List'[Product]), ([Price Actual]-[Price Budget])*[Volume Actual])

Volume effect: sumx(values('Product List'[Product]),([Volume Actual]-[Volume Budget])*[Price Budget]-[Mix Effect])

Mix effect:

var TotalActVolume=calculate([Volume Actual],allselected(Data)) 

var TotalBdgVolume=calculate([Volume Budget],allselected(Data))

var TotalBdgRevenue=calculate([Revenue Budget],allselected(Data))

var BudgPriceAvg=divide(TotalBdgRevenue,TotalBdgVolume)

Var BudgPriceDiff=[Price Budget]-BudgPriceAvg

var SalesMixAct=divide([Volume Actual],TotalActVolume) 

var SalesMixBdg=divide([Volume Budget],TotalBdgVolume)

return sumx(values('Product List'[Product]),TotalActVolume*(SalesMixAct-SalesMixBdg)*BudgPriceDiff)

The Dax for price effect seems to be working well. However the dax for mix effect is problematic with the amount for total row unable to be shown. The dax for volume effect is able to generate values on both individual and total row level but the values look incorrect probably associated with the error with the dax for mix effect.

Could you please help figure out a correct way of implementing the dax for mix effect or volume effect as well if it is problematic too? Thank you!

Link for the power bi file: https://jumpshare.com/v/P1F0Ch3yqSn9h0VFm5QY 

[josujimenez1994]

HI, 
I have the same problem. Did you solve it? 

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

I checked the file you provided. It is found that the sum of the results of (SalesMixAct-SalesMixBdg) is 0, which is the reason why the sum of Mix effect is 0. In addition, you can modify your measure as follows:

Mix effect = 
var TotalActVolume=calculate([Volume Actual],all(Data) )

var TotalBdgVolume=calculate([Volume Budget],ALL(Data))

var TotalBdgRevenue=calculate([Revenue Budget],ALL(Data))

var BudgPriceAvg=divide(TotalBdgRevenue,TotalBdgVolume)

Var BudgPriceDiff=CALCULATE(SUMX(VALUES('Product List'[Product]),[Price Budget]-BudgPriceAvg))

var SalesMixAct=divide([Volume Actual],TotalActVolume) 

var SalesMixBdg=divide([Volume Budget],TotalBdgVolume)

return SUMX(VALUES('Product List'[Product]),TotalActVolume*(SalesMixAct-SalesMixBdg)*BudgPriceDiff)

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

[amitchandak]

@Anonymous , In this last one

return sumx(values('Product List'[Product]),TotalActVolume*(SalesMixAct-SalesMixBdg)*BudgPriceDiff)

check it should be TotalActVolume or [Volume Actual] .

Also what is the expected output

[Anonymous]

Hi 

It is TotalActVolume not actual volume. Sorry for the confusion. The expected output is to show mix effect and volume effect correctly on both individual row and total row level. The amount in total row should be the sum of amount in individual rows. The sum of price, volume and mix effect equals "Revenue actual vs budget.  Thank you!

[amitchandak]

@Anonymous , Can you share the number, which should come. If you can explain like example

201*(103-105)*90

[Anonymous]

Hi 

Can you please check the link below for the excel version of the volume price mix analysis? The desired output is the same as the excel table. Thank you!

https://jumpshare.com/v/rIxZeEfJWNFUUbh90jHE