Price-Volume-Mix Variance Analysis In Power BI & Excel 💥 Step-By-Step | Zebra BI Webinar

Подписаться 15 тыс.

Просмотров 44 тыс.

50% 1

📢📢📢 BIG ANNOUNCEMENT:
Even faster & more flexible visual is there - Zebra BI for Office! (Yes, Excel AND PowerPoint🤯)
Try it for FREE
👉👉👉bit.ly/3rCPo0W👈👈👈
The Price-Volume-Mix (PVM) variance analysis is an extremely helpful method for explaining variances. Learn the efficient way how to implement it in both Power BI and Excel!
Download practical PBIX and Excel examples used in this tutorial here: zebrabi.com/price-volume-mix-...
✅ Subscribe to always get our latest videos: ru-vid.com?sub_c...
The Price-Volume-Mix (PVM) variance analysis is an extremely helpful method for explaining variances that will add a lot of value to your dashboards and reports. It's an excellent tool that every business analyst should add to his toolbox.
In this video you'll learn:
👉The benefits of Price-Volume-Mix variance analysis
👉How to prepare your data
👉How to visualize the variance analysis
👉How to control advanced settings of Zebra BI visuals with a theme
👉Step-by-step implementation in Power BI and Excel
(0:00) - Introduction by Andrej Lapajne
(2:11) - Variance example
(3:32) - What exactly is Mix?
(7:21) - Webinar Overview
(12:44) - PVM in Excel
(13:17) - Data requierments
(16:53) - The "Mix Change" method
(23:14) - Separate Volume and Mix effects
(29:58) - Method #2
(38:52) - Small multiples
(41:18) - PVM in Power BI
(42:23) - PowerQuery (creating Products table).
(44:37) - Relationship view
(45:25) - Price variance
(47:41) - Volume variance
(48:20) - Mix variance
(49:09) - Unpivot measures
(52:21) - Q&A
(55:52) - Including profits into the analysis
(59:05) - Definitions
(1:06:01) - PVM and customers
(1:08:46) - Conclusion
▸Use the same examples we do: zebrabi.com/pbix/
👋 With Zebra BI you can transform your Power BI reports into professional, interactive visualizations that tell a story. And the best part? You don't need advanced BI skills to get started.
👉👉👉 Try Zebra BI for Power BI for free today and see just how simple it is to create charts that get results: bit.ly/3ZMgwY4 👈👈👈
ABOUT OUR CHANNEL
Our channel is all about Actionable Reporting and has one goal: to enable everyone to create better reports. We cover Power BI, Excel, and PowerPoint reporting, business intelligence, business analytics, data storytelling, and related topics.
Check out our channel here:
/ @zebrabi
Don’t forget to subscribe!
CHECK OUT OUR OTHER VIDEOS
• Top 5 DAX tricks for s...
• Top 10 Most Effective ...
• TOP Excel & PowerPoint...
We make these awesome data visualization tools, check them out here:
zebrabi.com/power-bi-custom-v...
zebrabi.com/excel-add-in/
zebrabi.com/powerpoint-add-in/
FIND US AT
www.zebrabi.com/
GET IN TOUCH
Contact us on info@zebrabi.com
FOLLOW US ON SOCIAL
Get updates & reach out on our Social Media Profiles!
Twitter: / zebrabi
Facebook: / zebra.bi
LinkedIn: / 5051039

Наука

Опубликовано:

2 авг 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 62

@ankushthukral10 2 года назад

It was very helpful. It is exactly what i need. You nailed it. Thanks for spreading knowledge and love. take care

@ZebraBI 2 года назад

Thank you so much for your comment, we're glad we could help! :)

@HebertAG 3 месяца назад

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

@amospan14 9 месяцев назад

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. =)

@AndreLMSantos 2 года назад

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! 👍

@cevikyi 2 года назад

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!

@tessnorman 2 года назад

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)?

@borischivapallottole 2 года назад

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 :)

@arjunjalan2007 Год назад

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?

@efthimiosspiridopoulos9531 3 года назад

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 3 года назад

You have to sort the "Category" based on "ID" in the PWM table. Worked for me.

@ZebraBI 3 года назад

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

@PatriceDescaillot 2 года назад

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 :(

@PatriceDescaillot 2 года назад

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 !!!

@liorrahav7463 3 года назад

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?

@liorrahav7463 3 года назад

I watched the video again and you explain this issue that you can't show a mix at the description level :-) thanks.

@ZebraBI 3 года назад

@@liorrahav7463 glad to hear that this was sorted out. Let us know if you need anything else!

@yoismelperez2744 6 месяцев назад

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?

@ZebraBI 6 месяцев назад

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.

@firmanwijaya163 3 года назад

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!

@ankushthukral10 2 года назад

you need to calculate regional mix also.

@alexl0l0l0 2 месяца назад

Hey! By any chance did you figure out how to include channel and country mix? I'm having the exact same issue. Thank you!

@lenapankova773 3 года назад

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"

@ZebraBI 3 года назад

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!

@karikoga320 3 года назад

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

@kamilwantuch8991 3 года назад

Dear Zebra BI, I'm also having some doubts on this. Could you please comment? Thank you.

@NL-tq1yr 2 года назад

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.

@jamalbaghirov2855 3 года назад

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.

@valentinyosef5940 3 года назад

i know it's kinda randomly asking but does anybody know of a good website to watch new series online?

@maddoxrussell8970 3 года назад

@Valentin Yosef Ehh try flixportal. just search on google for it :) -maddox

@valentinyosef5940 3 года назад

@Maddox Russell Thank you, I signed up and it seems like a nice service :D I really appreciate it !!

@lenapankova773 3 года назад

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 3 года назад

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!

@kamilwantuch8991 3 года назад

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

@ZebraBI 3 года назад

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

@anwarar677 3 года назад

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

@PatriceDescaillot 2 года назад

Not sure at all... But probably to do the SUMX with Product + Country.... Then you have a Product & Geo Mix as a result.

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

@alexdupont6924 3 месяца назад

how would you deal with items there was not sold last year? Som ACT = revenue 1000 PY=0?

@ZebraBI 2 месяца назад

They go under NEW.

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

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

@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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-cRDPoVkfJvE.html

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

@Sowmya465 11 месяцев назад

Is this same as Waterfall chart?

@ZebraBI 11 месяцев назад

Yes the data is shown on a waterfall chart. More about waterfall charts: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-QALUtL8zS1I.html

@ramzy858 2 года назад

Sorry to say but the way you calculate your mix effect is very wrong.

@PhrankTube 2 года назад

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 "RU-vid Reply" does not allow me to give a website reply. Sorry . . . .