Variance Analysis in Power BI

[C_Mucke4]

Hi

I would like to solve a variance analysis problem in Power BI which I could do in Excel, but it is less interactive.

So I would like to implement a 'mix' element in the variance analyis. E.g. I would like to break down the YoY net sales variance to volume-mix-price. Price would be the price difference times the new quantity.

The mix is the most important here. I would like to see how the product mix changes within a given customer. To do that, I will need to have a growth rate of the customer and I will have to compare it to the product growth.

E.g. there is a customer buying two products, A and B. They bought 20 of A and they bought 5 of B as well, so total quantity is 25, share of A is 80%. Next year, they bought 25 of A but they bought 25 of B as well. So the quantity of A has increased but its share within the mix is down to 50%. If the price of A is USD 100, then net sales would be USD 2,000 in the first year and USD 2,500 in the second. (To make this example simple,  let's assume that there is no price change.) The variance is USD 500. 

So the quantity sold to this customer increased by 100% (from 25 to 50), but Product A grew only by 25%, therefore there is a negative mix variance on this product. If A had grown by 100% as well, the sales would have been USD 4,000, the total YoY net sales would have increased by 2,000 if it weren't for the change in mix. That brings is down to USD 500.

Volume: USD 2,000

Mix: USD (1,500)

Price: nil

Total net sales variance: USD 500

Do you have any idea how I could do this in Power BI? I was not able to apply customer growth to products... It would be even better if this can be flexible, let's say I want to see the mix of different versions of each product, I could do that by drilling down. Is there any way?

Thank you

[v-chuncz-msft]

@C_Mucke4,

Based on my experience, drill down is an appropriate way. You may also submit an idea via https://ideas.powerbi.com/forums/265200-power-bi.

[C_Mucke4]

@v-chuncz-msftThank you for your reply, I will submit this as an idea. But do you have any opinion how this could be solved in DAX? Drilling down is only a dream at the moment, it would be great to have on customer level first where every customer have their own growth rate.

Thank you,

Csaba

[v-chuncz-msft]

@C_Mucke4,

Show us a complete example and the expected output more clearly.

[C_Mucke4]

@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.