Variance Analysis in Power BI

v-chuncz-msftYou can see one example below:

Sales variance is broken down to volume, mix and price. Total mix on customer level is the sum of each mix variance relating to a given product. As the mix has not changed in case of Customer 1, you cannot see any result on mix. But in case of Customer 2, the share of each product within sales mix got changed: share of product A has fallen from 80% to 37.5%, while share of Product B has increased from 20% to 62.5%. Therefore you can see a negative mix variance in case of Product A, positive in case of Product B and a positive mix effect in case of Customer 2. As the total volume of Customer 2 increased by 60%, if the share of the products would have risen by 60%, the mix variance would be zero as the mix would be unchanged.

(Rows 5 and 8 are simply the sum of the rows above them.)

This means when calculating the mix variance a different 'growth' rate shall be applied to each products per customer and I was not able to find an automated way to allocate a 'customer level' volume growth to each product, although this can be done in Excel. This would be the first step, to create a table identical to the above in Power BI.

The second step would be changing levels or perspecitve, e.g. if I wanted to see different pack sizes, delivery locations, etc instead of products themselves or even one level higher, mix changes between customers.

Hope it is more easy to understand.

Thank your help.