Volume - Price - Mix Effects - Sample DB

[LucaA]

Hi All,

i would need help on the split of volume, price and mix effects over 2 different level. Here below the image of my current situation and i also attach the PBI file.

In the first section (green one) you can see the solution with just volume and price effects. then when i include the mix i start having problems (mix total is wrong).

Moreover i would like to include these effects in a bridge visual but i am not able to do that using this "measure approach" 

@mahoneypati created another thread, i tried the solution but it is working perfectly (probably i am doing something wrong)

https://www.dropbox.com/s/ejlrtyeb0aotnpt/Prova%20Volume%20Price%20Mix.pbix?dl=0

[PaulDBrown]

@LucaA 

Hi there. I don't quite understand what the calculations are? Can you please explain what you are trying to calculate and what the relavant fields?

[LucaA]

@PaulDBrown  hi,

column 1 --> net total delta

column 2 and 3 --> split of total delta between price effect (2) and volume effect (3)

from column 4 i tried to include mix effect -->

(keep considering column 2 as price effect)

column 4 --> volume effect

column 5 --> mix effect 

column 6 --> total delta summing up column 2(price), column 4 (volume) and 5 (mix)

you can see that in column 2 and 4 deltas at product level sum up correctly, this is not true for column 5 (mix). This leads to a wrong column 6.

Moreover i would be able to bring everything into a bridge chart, but calculating everything with measures i am not able to do that.

Thanks in advance

Luca

[v-jingzhang]

Hi @LucaA 

There is a logic error in [Mix Effect Final2] measure. When it calculates for a category, the values of [Mix Ratio CY] and [Mix Ratio PY] are both 100% so the difference between them is 0, making the mix total value is 0.