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
