cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
C_Mucke4 Frequent Visitor
Frequent Visitor

Flexible variance analyis

Hi, I have been trying to solve a problem and make it as automatic as possible in Power BI - with limited success so far however I could do it in Excel.

 

I am trying to build up a volume-mix-price variance analyis of net sales, I would like to see net sales YoY variance broken to these categories.

 

Price variance is simply the price difference times the new quantity.

 

Traditional volume difference is split into two categories: volume and mix. This 'new' volume variance shows what the new quantity would be if the given product had the same growth ('growth' can be negative) as the whole category (which is the customer in this case). The 'mix variance' shows the effect of not having the same growth as the whole category. The total of these two would be the difference in quantity times the old price.

 

I have the following measures:

Q1: total quantity of given subcategory in the previous period

Q1B: total quantity of given subcategory in the previous period multiplied by the % change of the category

Q2: total quantity of given subcategory in the current period

P1: old price

P2: new price

 

The 'subcategory' is the key here. I would like to apply the growth of the subcategory to the products below it, e.g. the customer's total growth to the products they bought so that I can that see if the share of a given procuduct in mix has increased or decreased. This would be the first step.

 

The second would be even bigger, to make it flexible. E.g. to see how mix changes when I drill down, e.g. if I want to see the mix of different variants within a product or a level higher, mix of customers in a region, etc.

 

Do you have any idea how this can be done?

 

Thank you