This is an excellent video covering price-volume-mix variance analysis. Very well structured and clearly articulated. Thank you for the walkthrough in Excel and Power BI. =)
Hi Andrej, thanks for the great video. Although your approach (especially the advanced one) is very useful, there are 2 main areas I can not figure out: 1. Discontinued products are shown in negatives. Aren’t they supposed to be 0 only? (No effect) 2. Can you elaborate more how to seperate mix and volume from each other? I feel like they’re somehow connected Thanks!
Hi, super helpful video! Just a quick question : For Mix analysis, shoulndt the formula be Total AC Quantity * Price Deviation * Mix Change ? Currently the quantity is being divided by price which seems to be the opposite right ?
I was able to follow along and create a similar report, thank you. Just can't figure out how to get the colors show red and green in the Zebra BI table. I already have Style set to Custom and I am using Positive color green, Negative color red. For the table itself, I have ProductType in Category, and Category in Group, and Revenue PVM in Values. Any suggestions?
The red and green for positive and negatives will out of the box only be shown for variances. Be sure to check that you are coloring the variances. If you still have problems please write to our support.
Thanks for this video, Andrej. Great job. However, I believe something may not be right in the Excel file ("Mix Change method"): (1) You stated that "Mix = SUM(Quantity AC) / (Price PY - Price PY / SUM(Price PY)) * Mix Change"... (2) ... However, on Column M you used the denominator: " / (Price PY - SUM(Revenue PY) / SUM(Quantity PY) ) ". But SUM(Revenue PY) = SUM(Price PY) * SUM(Quantity PY). Formula (2) can then be re-written as: (2a) "Mix = SUM(Quantity AC) / (Price PY - SUM(Price PY) ) * Mix Change", which is totally different from formula (1). (3) In addition, Total Revenues, Price Impact and Volume Impact all have a "revenues" dimension (i.e., Qtty x Price), which is expected. On the other hand, your Mix Impact has a strange dimension: Qtty / Price. So, in the end it's like saying: Revenue AC ($) = Revenue PY ($) + Impacts of Price ($) + Vol. ($) + Mix (units per $)... Could you please clarify? Much appreciated! 👍
Hi Zebra , This is wonderful , big like 👍🏻. I have questions , In order to come with a PVM analysis, I believe you must know the budget price quantity For the current year performers and then you can analyze versus actual then the weighted average volume from the budget quantity into the mix price . Is this true what I have mentioned above in case you don’t have PVM calculated from the last year?
I didn't go over all the different methods but here's a good way to check if the method is good or not. If the total of all the effects on the row level doesn't equal the total variance on the row level them it's not a good method. The only method I know that does this is the Harvard method.
Thanks for the video. Very helpful. Could you please also make a video for PVM in percentage terms? For example, if the gross margin is 40% for CY and 35% for PY, +2.5% was due to price, 0% due to volume, 2% due to mix, etc.
Hello everyone, thank you for the presentation, it was very helpful. I am currently thinking about the pros and cons of the different approaches. Can somebody explain why the "advanced" approach is preferred? Because if you look at the product level the volume effect does not add up to the subtotals? This could lead to confusion with report recipients. What do you think about this? Thank you :)
Thanks for this great detailed video. Could you also share the calculations for a Gross Margin Variance PVM Driver Analysis (as opposed to this one which is for Change in Sales)?
Hi Andre, regarding PVM for gross profit, you mention that you replace Price with Unit Profit. I am not sure if that is the case, you still need Price in the PVM analysis. I think one needs to add the cost impact i.e. change in unit cost of sales to the existing PVM and adjust other PVM measures to reflect gross profit and not revenue
Wonderful video ! Many thanks for the sharing. I learnt a lot I like the method 2 definitely, but you need "standardize" volume unit to run it and get the total Mix calculation working. Because if you applied it on a portfolio of product with heterogenous volume unit, it will not work :(
I think I got it.... If you don't have "comparable" Product Group , you can apply the method 2 within Product Group, and then sum up the 5 components of Sub totals Product Group. As a result the Vol and Mix will be different as you would exculde the mix between Product Group but keep only the mix within each Product Group. Which could be probably still relevant, if you want to ensure the mix is driven by Product Group manager Sorry for the thinking loud but helping me to be sure I understand correctly the logic. Thanks again for the sharing after years looking at thing on PVM on Internet, this video/explanation is definitely the best one I found so far !!!
The waterfall chart is always sorted by categories, so by the sorting set on the category column of the data table in Power BI. This means if your PVM column isn't sorted by a certain order (ID column) it will sort the categories alphabetically. You can learn more about custom sorting in this article help.zebrabi.com/kb/power-bi/sort-by-custom-sort/
This is an awesome tool! However, I'm struggling with Mix. Can we get a more in-depth explanation of mix and how it presents in a table? My results aren't making sense. My delta at the top level does not match the sum of my deltas from the level below.
Thanks for this great video! is it possible that your mix calculation at the group level is wrong (a lot of 0's) while at the material description level you are correct?
Hi Andrej, I really appreciate your free webinars. It’a a great help. This one ("Price-Volume-Mix Variance Analysis") is especially useful. But in example downloaded Zebra BI table with explanations doesn’t work (the worksheet “PVM + explanations”) When I try to replicate it I can’t create such visual/Because when I put ‘category long’ in the field ‘GROUP” Zebra displays it horizontally not vertically ( as in your file. So I get in rows product group on;y and a lot of columns ( "price", "volume", "mix" an so on instead of headings) . Could you please help me with this problem ?
@@ZebraBI Hi, I'm having the same problem. I can't see the chart in Power BI desktop. It's visible in the browser version. Could you please advise how to fix it? Thank you.
@@kamilwantuch8991 We recommend upgrading your Power BI Desktop to the latest version. The same goes if you are not using the latest version of Power BI Report Server.
And one more question) When I use visualization "ZEBRA BI TABLE" and select a waterfall view in Zebra BI table and put several factors in the field ‘Category’ the subtotal is always BELOW the children items. It’s very inconvenient. Is it possible to move it above ? (I see such option only in case of view "Table" ( in settings appears "show rows subtotals" and it's possible to choose "above/below"
Thank you Andreij, great anlalysis method. I'm struggling though to sort Revenue by PVM as in you demo. I cannot seem to get Revenue PY on the beginning and Revenue AC on the far right part of the Zebra BI Charts although settings are exactly the same as per your pbix file. Any ideas?
@@iamjeffreykuipers thank you for jumping in! That's exactly the right way to do it :) Efthimios, for the correct sorting you need to: (1) set up the sort order in your Power BI data model by using a separate data field for sorting (called "ID" in Andrej's example) (2) apply the sort on the visual by clicking on the three-dot menu (...)
Great video. I have one doubt. I think here in your table the price is same for all countries. If the prices are varies in countries what are the changes we need to do? Please advice
Hi Andrej! thank you for the insightful video! I would like to ask, what if i also have channel/country mix which means that my price for the same product in specific channel or country are different? What should I do? Your help is highly appreciated, thank you!
Your analysis is ambiguous. You mentioned that you will provide an excel file of the calculation. I have not found any also downloaded an excel file with no calculations.
Hi, imagine that i do variance of quantity at product level and channel level, for discontinued product, quantity variance by product is correct but quantity variance by channel is mistake by the amount of discontinued product, can you explain this ?
Hi. You said in your video, that both table structures are possible. having prior year revenues in a separate column or revenues in one column with dates in a different one. i'm having the latter one out of my DWH. But I only managed to have a measure as prior year revenue (not a separate column). But with measures, it is not possible to calculate new revenue of new products. The formula New Revenue = calculate(sum(Sales[Revenue]), Filter(Sales, [Revenue PY] = 0)) didn't work How do I transorm this table with just one column for revenue into two columns (revenue and prior year revenue)?
I don't know if I understand you correctly but if you have a calendar table you could use something like Revenues PY = CALCULATE ( [revenues AC], DATEADD ( 'Calendar'[Date], -1, Year ) ).
This is way too complicated! The answer for mix variance lies in applying some simple and easy to understand basic math. The simple math dynamic at the heart of Mix Variance is the delta (Budget Profit Rate minus Total Budget Profit Rate). Any methodology which does NOT directly compare the delta of a group's aggregate profit rate with that of its' individual constituent component's profit rate is patently incorrect. From the video and its' explanation, it is very difficult to determine if this is being correctly applied . . . . or not. This correct methodology provides the ability to drill down to each individual constituent component's contribution to the total mix variance of its' group.
I'm really struggling with mix. I get one number in a product category level, but the pieces of the category do not total the category total. Can you expand your comment or share your change to mix?
@@brettsmith5508 Companies are organized into hierarchical structures. For example, Products roll up into Groups; Groups roll up into Divisions; Divisions roll up into a fully consolidated Company. In order to provide mathematical consistency (as well as an all important drill down capability), the use of a Rollup Variance accumulator is required. Rollup Variance functions as an accumlator of Mix Variances. Rollup Variance, at any level of consolidation is equal to [Rollup Variance plus Mix Variance] accumulation brought forward from the immediately preceding lower consolidation level. Note that Rollup Variance accumulation starts at 0 at the lowest (Product) level of the consolidation hierarchy.
@@PhrankTube I’ll love what this zebra pvm demonstrates but mix seems useless in this form. What you describe seems exactly what I’m missing. Can you point me to any examples?
It was very helpful. It is exactly what i need. You nailed it. Thanks for spreading knowledge and love. take care
Thank you so much for your comment, we're glad we could help! :)
Thank you very much, I've been struggling trying to pass the excel analysis to the powerBI and adding the values correctly. Now work like a charm
This is an excellent video covering price-volume-mix variance analysis. Very well structured and clearly articulated. Thank you for the walkthrough in Excel and Power BI. =)
Hi Andrej, thanks for the great video. Although your approach (especially the advanced one) is very useful, there are 2 main areas I can not figure out:
1. Discontinued products are shown in negatives. Aren’t they supposed to be 0 only? (No effect)
2. Can you elaborate more how to seperate mix and volume from each other? I feel like they’re somehow connected
Thanks!
Hi, super helpful video! Just a quick question : For Mix analysis, shoulndt the formula be Total AC Quantity * Price Deviation * Mix Change ? Currently the quantity is being divided by price which seems to be the opposite right ?
This is baffling me too! The current formula he's using makes no sense to me
I was able to follow along and create a similar report, thank you. Just can't figure out how to get the colors show red and green in the Zebra BI table. I already have Style set to Custom and I am using Positive color green, Negative color red. For the table itself, I have ProductType in Category, and Category in Group, and Revenue PVM in Values. Any suggestions?
The red and green for positive and negatives will out of the box only be shown for variances. Be sure to check that you are coloring the variances. If you still have problems please write to our support.
Thanks for this video, Andrej. Great job. However, I believe something may not be right in the Excel file ("Mix Change method"):
(1) You stated that "Mix = SUM(Quantity AC) / (Price PY - Price PY / SUM(Price PY)) * Mix Change"...
(2) ... However, on Column M you used the denominator: " / (Price PY - SUM(Revenue PY) / SUM(Quantity PY) ) ".
But SUM(Revenue PY) = SUM(Price PY) * SUM(Quantity PY). Formula (2) can then be re-written as:
(2a) "Mix = SUM(Quantity AC) / (Price PY - SUM(Price PY) ) * Mix Change",
which is totally different from formula (1).
(3) In addition, Total Revenues, Price Impact and Volume Impact all have a "revenues" dimension (i.e., Qtty x Price), which is expected.
On the other hand, your Mix Impact has a strange dimension: Qtty / Price.
So, in the end it's like saying: Revenue AC ($) = Revenue PY ($) + Impacts of Price ($) + Vol. ($) + Mix (units per $)...
Could you please clarify? Much appreciated! 👍
Yes, thank you. That mix formula was confusing me too
Hi Zebra ,
This is wonderful , big like 👍🏻. I have questions , In order to come with a PVM analysis, I believe you must know the budget price quantity For the current year performers and then you can analyze versus actual then the weighted average volume from the budget quantity into the mix price . Is this true what I have mentioned above in case you don’t have PVM calculated from the last year?
I didn't go over all the different methods but here's a good way to check if the method is good or not. If the total of all the effects on the row level doesn't equal the total variance on the row level them it's not a good method.
The only method I know that does this is the Harvard method.
Hi thanks for this video! could you please show us the DAX measure for [New] and [Discontinued]? thanks!
Thanks for the video. Very helpful. Could you please also make a video for PVM in percentage terms? For example, if the gross margin is 40% for CY and 35% for PY, +2.5% was due to price, 0% due to volume, 2% due to mix, etc.
i know it's kinda randomly asking but does anybody know of a good website to watch new series online?
@Valentin Yosef Ehh try flixportal. just search on google for it :) -maddox
@Maddox Russell Thank you, I signed up and it seems like a nice service :D I really appreciate it !!
Hello everyone, thank you for the presentation, it was very helpful. I am currently thinking about the pros and cons of the different approaches.
Can somebody explain why the "advanced" approach is preferred? Because if you look at the product level the volume effect does not add up to the subtotals?
This could lead to confusion with report recipients.
What do you think about this?
Thank you :)
Thanks for this great detailed video. Could you also share the calculations for a Gross Margin Variance PVM Driver Analysis (as opposed to this one which is for Change in Sales)?
Hi Andre, regarding PVM for gross profit, you mention that you replace Price with Unit Profit. I am not sure if that is the case, you still need Price in the PVM analysis. I think one needs to add the cost impact i.e. change in unit cost of sales to the existing PVM and adjust other PVM measures to reflect gross profit and not revenue
Dear Zebra BI, I'm also having some doubts on this. Could you please comment? Thank you.
Wonderful video ! Many thanks for the sharing. I learnt a lot
I like the method 2 definitely, but you need "standardize" volume unit to run it and get the total Mix calculation working.
Because if you applied it on a portfolio of product with heterogenous volume unit, it will not work :(
I think I got it....
If you don't have "comparable" Product Group , you can apply the method 2 within Product Group, and then sum up the 5 components of Sub totals Product Group.
As a result the Vol and Mix will be different as you would exculde the mix between Product Group but keep only the mix within each Product Group.
Which could be probably still relevant, if you want to ensure the mix is driven by Product Group manager
Sorry for the thinking loud but helping me to be sure I understand correctly the logic.
Thanks again for the sharing after years looking at thing on PVM on Internet, this video/explanation is definitely the best one I found so far !!!
How did you turn off sorting on the waterfall visual?
The waterfall chart is always sorted by categories, so by the sorting set on the category column of the data table in Power BI. This means if your PVM column isn't sorted by a certain order (ID column) it will sort the categories alphabetically. You can learn more about custom sorting in this article help.zebrabi.com/kb/power-bi/sort-by-custom-sort/
This is an awesome tool! However, I'm struggling with Mix. Can we get a more in-depth explanation of mix and how it presents in a table? My results aren't making sense. My delta at the top level does not match the sum of my deltas from the level below.
Hi,
How are you doing the mix part? It should simply be the remainder of your variance as Andrej shows in the second option.
Thanks for this great video! is it possible that your mix calculation at the group level is wrong (a lot of 0's) while at the material description level you are correct?
I watched the video again and you explain this issue that you can't show a mix at the description level :-) thanks.
@@liorrahav7463 glad to hear that this was sorted out. Let us know if you need anything else!
Hi Andrej,
I really appreciate your free webinars. It’a a great help. This one ("Price-Volume-Mix Variance Analysis") is especially useful.
But in example downloaded Zebra BI table with explanations doesn’t work (the worksheet “PVM + explanations”)
When I try to replicate it I can’t create such visual/Because when I put ‘category long’ in the field ‘GROUP” Zebra displays it horizontally not vertically ( as in your file. So I get in rows product group on;y and a lot of columns ( "price", "volume", "mix" an so on instead of headings) .
Could you please help me with this problem ?
Hi Lena, thanks for your comment and for reaching out directly to our support team to handle this via email. We're always happy to help!
@@ZebraBI Hi, I'm having the same problem. I can't see the chart in Power BI desktop. It's visible in the browser version. Could you please advise how to fix it? Thank you.
@@kamilwantuch8991 We recommend upgrading your Power BI Desktop to the latest version. The same goes if you are not using the latest version of Power BI Report Server.
how would you deal with items there was not sold last year? Som ACT = revenue 1000 PY=0?
They go under NEW.
And one more question) When I use visualization "ZEBRA BI TABLE" and select a waterfall view in Zebra BI table and put several factors in the field ‘Category’ the subtotal is always BELOW the children items. It’s very inconvenient. Is it possible to move it above ? (I see such option only in case of view "Table" ( in settings appears "show rows subtotals" and it's possible to choose "above/below"
Hi Lena, thanks for your comment and for reaching out directly to our support team to handle this via email. We're always happy to help!
Thank you Andreij, great anlalysis method.
I'm struggling though to sort Revenue by PVM as in you demo. I cannot seem to get Revenue PY on the beginning and Revenue AC on the far right part of the Zebra BI Charts although settings are exactly the same as per your pbix file. Any ideas?
You have to sort the "Category" based on "ID" in the PWM table. Worked for me.
@@iamjeffreykuipers thank you for jumping in! That's exactly the right way to do it :) Efthimios, for the correct sorting you need to:
(1) set up the sort order in your Power BI data model by using a separate data field for sorting (called "ID" in Andrej's example)
(2) apply the sort on the visual by clicking on the three-dot menu (...)
Great video. I have one doubt. I think here in your table the price is same for all countries. If the prices are varies in countries what are the changes we need to do?
Please advice
Not sure at all... But probably to do the SUMX with Product + Country.... Then you have a Product & Geo Mix as a result.
Hi Andrej! thank you for the insightful video! I would like to ask, what if i also have channel/country mix which means that my price for the same product in specific channel or country are different? What should I do? Your help is highly appreciated, thank you!
you need to calculate regional mix also.
Hey! By any chance did you figure out how to include channel and country mix? I'm having the exact same issue. Thank you!
Your analysis is ambiguous. You mentioned that you will provide an excel file of the calculation. I have not found any also downloaded an excel file with no calculations.
Hi, have you tried downloading it from our website - the URL is in the video description? Is it not working for you?
Hi, imagine that i do variance of quantity at product level and channel level, for discontinued product, quantity variance by product is correct but quantity variance by channel is mistake by the amount of discontinued product, can you explain this ?
Hi, can you please be so kind and send us an email with more details on support@zebrabi.com ? Thank you in advance.
Hi. You said in your video, that both table structures are possible. having prior year revenues in a separate column or revenues in one column with dates in a different one. i'm having the latter one out of my DWH. But I only managed to have a measure as prior year revenue (not a separate column). But with measures, it is not possible to calculate new revenue of new products. The formula New Revenue = calculate(sum(Sales[Revenue]), Filter(Sales, [Revenue PY] = 0)) didn't work
How do I transorm this table with just one column for revenue into two columns (revenue and prior year revenue)?
I don't know if I understand you correctly but if you have a calendar table you could use something like Revenues PY = CALCULATE ( [revenues AC], DATEADD ( 'Calendar'[Date], -1, Year ) ).
You can also have a look at this video as it also prepares the comparison calculation columns: ruclips.net/video/cRDPoVkfJvE/видео.html
Is this same as Waterfall chart?
Yes the data is shown on a waterfall chart. More about waterfall charts: ruclips.net/video/QALUtL8zS1I/видео.html
Simone Prairie
Sorry to say but the way you calculate your mix effect is very wrong.
This is way too complicated! The answer for mix variance lies in applying some simple and easy to understand basic math. The simple math dynamic at the heart of Mix Variance is the delta (Budget Profit Rate minus Total Budget Profit Rate).
Any methodology which does NOT directly compare the delta of a group's aggregate profit rate with that of its' individual constituent component's profit rate is patently incorrect. From the video and its' explanation, it is very difficult to determine if this is being correctly applied . . . . or not. This correct methodology provides the ability to drill down to each individual constituent component's contribution to the total mix variance of its' group.
I'm really struggling with mix. I get one number in a product category level, but the pieces of the category do not total the category total. Can you expand your comment or share your change to mix?
@@brettsmith5508 Companies are organized into hierarchical structures. For example, Products roll up into Groups; Groups roll up into Divisions; Divisions roll up into a fully consolidated Company. In order to provide mathematical consistency (as well as an all important drill down capability), the use of a Rollup Variance accumulator is required. Rollup Variance functions as an accumlator of Mix Variances. Rollup Variance, at any level of consolidation is equal to [Rollup Variance plus Mix Variance] accumulation brought forward from the immediately preceding lower consolidation level. Note that Rollup Variance accumulation starts at 0 at the lowest (Product) level of the consolidation hierarchy.
My best suggestion . . . . do a search for "Rollup Variance". Hope this helps . . . . . .
@@PhrankTube I’ll love what this zebra pvm demonstrates but mix seems useless in this form. What you describe seems exactly what I’m missing. Can you point me to any examples?
@@brettsmith5508 This is the best that I can do because a "RUclips Reply" does not allow me to give a website reply. Sorry . . . .