It was very helpful. It is exactly what i need. You nailed it. Thanks for spreading knowledge and love. take care
@ZebraBI2 жыл бұрын
Thank you so much for your comment, we're glad we could help! :)
@HebertAG4 ай бұрын
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
@amospan149 ай бұрын
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. =)
@cevikyi2 жыл бұрын
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!
@AndreLMSantos2 жыл бұрын
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! 👍
@arjunjalan20072 жыл бұрын
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 ?
@anonje Жыл бұрын
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?
@yoismelperez27447 ай бұрын
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?
@ZebraBI7 ай бұрын
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.
@borischivapallottole2 жыл бұрын
Hi thanks for this video! could you please show us the DAX measure for [New] and [Discontinued]? thanks!
@MrJadison7 Жыл бұрын
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 :)
@jamalbaghirov28553 жыл бұрын
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.
@valentinyosef59403 жыл бұрын
i know it's kinda randomly asking but does anybody know of a good website to watch new series online?
@maddoxrussell89703 жыл бұрын
@Valentin Yosef Ehh try flixportal. just search on google for it :) -maddox
@valentinyosef59403 жыл бұрын
@Maddox Russell Thank you, I signed up and it seems like a nice service :D I really appreciate it !!
@tessnorman2 жыл бұрын
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)?
@NL-tq1yr2 жыл бұрын
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.
@liorrahav74633 жыл бұрын
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?
@liorrahav74633 жыл бұрын
I watched the video again and you explain this issue that you can't show a mix at the description level :-) thanks.
@ZebraBI3 жыл бұрын
@@liorrahav7463 glad to hear that this was sorted out. Let us know if you need anything else!
@karikoga3203 жыл бұрын
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
@kamilwantuch89913 жыл бұрын
Dear Zebra BI, I'm also having some doubts on this. Could you please comment? Thank you.
@PatriceDescaillot2 жыл бұрын
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 :(
@PatriceDescaillot2 жыл бұрын
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 !!!
@brettsmith5508 Жыл бұрын
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.
@ZebraBI Жыл бұрын
Hi, How are you doing the mix part? It should simply be the remainder of your variance as Andrej shows in the second option.
@lenapankova7733 жыл бұрын
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 ?
@ZebraBI3 жыл бұрын
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!
@kamilwantuch89913 жыл бұрын
@@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.
@ZebraBI3 жыл бұрын
@@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.
@alexdupont69243 ай бұрын
how would you deal with items there was not sold last year? Som ACT = revenue 1000 PY=0?
@ZebraBI3 ай бұрын
They go under NEW.
@anwarar6773 жыл бұрын
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
@PatriceDescaillot2 жыл бұрын
Not sure at all... But probably to do the SUMX with Product + Country.... Then you have a Product & Geo Mix as a result.
@lenapankova7733 жыл бұрын
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"
@ZebraBI3 жыл бұрын
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!
@scokim Жыл бұрын
How did you turn off sorting on the waterfall visual?
@ZebraBI Жыл бұрын
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/
@NilayMukhopadhyay Жыл бұрын
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.
@ZebraBI Жыл бұрын
Hi, have you tried downloading it from our website - the URL is in the video description? Is it not working for you?
@firmanwijaya1633 жыл бұрын
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!
@ankushthukral102 жыл бұрын
you need to calculate regional mix also.
@alexl0l0l02 ай бұрын
Hey! By any chance did you figure out how to include channel and country mix? I'm having the exact same issue. Thank you!
@angthong2691 Жыл бұрын
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 ?
@ZebraBI Жыл бұрын
Hi, can you please be so kind and send us an email with more details on support@zebrabi.com ? Thank you in advance.
@efthimiosspiridopoulos95313 жыл бұрын
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?
@iamjeffreykuipers3 жыл бұрын
You have to sort the "Category" based on "ID" in the PWM table. Worked for me.
@ZebraBI3 жыл бұрын
@@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 (...)
@northstreet79 Жыл бұрын
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)?
@ZebraBI Жыл бұрын
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 ) ).
@ZebraBI Жыл бұрын
You can also have a look at this video as it also prepares the comparison calculation columns: kzfaq.info/get/bejne/mbh0g9KH0Myupnk.html
@Sowmya465 Жыл бұрын
Is this same as Waterfall chart?
@ZebraBI Жыл бұрын
Yes the data is shown on a waterfall chart. More about waterfall charts: kzfaq.info/get/bejne/h6d8iNd9neC3YX0.html
@ramzy8582 жыл бұрын
Sorry to say but the way you calculate your mix effect is very wrong.
@PhrankTube2 жыл бұрын
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.
@brettsmith5508 Жыл бұрын
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?
@PhrankTube Жыл бұрын
@@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 Жыл бұрын
My best suggestion . . . . do a search for "Rollup Variance". Hope this helps . . . . . .
@brettsmith5508 Жыл бұрын
@@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?
@PhrankTube Жыл бұрын
@@brettsmith5508 This is the best that I can do because a "KZfaq Reply" does not allow me to give a website reply. Sorry . . . .