EDIT 2020-12-14: if you are looking for the perfect mathematical formula and the DAX necessary to implement PVM, please go the latest post/video in this series – DAX for Perfect Price Volume Mix (PVM) Calculation (Power BI Tutorial)
In the Part I of this series, I mentioned that I was cutting some corners with my calculation for the sake of giving you a simple and performant formula that was “good enough”. Well, what if it is not good enough and you will not settle for anything other than the perfect math???
Well, the good news is that it took me half the time of the Part I Video to cover the perfect formula in the video for the Part II. It takes a few more steps and it will take a bit of performance out of your reports, but this guide would not really be The Definitive one if I did not give you the perfect calculation, would it???
business intelligist 
Your video is just spectacular. But… how could I add one more variable, such as Customer? I´ve made that on the modelling, building the table ( such as: crossjoing(product, customer)), but… the modelling doesn´t work like in Excel. Could you give me a TIP?
I will be posting a PBIX file with all the calls in a day or two… hopefully it will have everything you need
Or are asking how to do the same but for customer mix?
No… I am wondering to make a table with two variables: Customer and Product. I´ve tried to add that in the modeling, but as I told you it doesn´t work. The “Crossjoing” function is not working very well.
That´s the modeling… using exactly the same structure you´ve posted… but adding two variables (customer and product) instead of only Product.
look below… I am showing only in part of the chart.
var TAB = CROSSJOIN(DISTINCT(CUST_GROUP), DISTINCT(Product))
VAR PriceImpact =
ROUND(
SUMX(
ADDCOLUMNS(TAB,
“Price”,
iF(ISBLANK([AUP PY]) || ISBLANK([AUP]),0,[AUP]-[AUP PY] )* [QTY_PY])
,[Price])
,2)
A very nice presentation indeed. I do think that your students might find the downloadable spreadsheet to be an easier introduction into these complexities. The calculation of mix is the important issue here and is thoroughly explained in The math is the same whether one is analyzing Gross Margin or, in your case, Sales. The site also includes information regarding the “real world” problem of consolidation with downloadable spreadsheets and a “Deep Dive” .pdf.
The simple math dynamic at the heart of Sales Mix Variance is the delta (Last Year’s Price minus Last Year’s Aggregate Price).
Any methodology which does NOT directly compare the delta of a group’s aggregate sales price with that of its’ individual constituent product’s sales price is both patently incorrect and very confusing. This is true whether one uses Power BI, an Excel spreadsheet, SQL . . . . . or an Abacus. The math is simple and easily understood . . . . just let it do the work for you.
This correct methodology provides the ability to drill down to each individual constituent product’s contribution to the total mix variance.
I really enjoyed this post. You really write a good blog.
I found your post interesting to read. I can’t wait to see your post soon. Good Luck with the upcoming update. This article is really very interesting and effective.
I found your post interesting to read. I can’t wait to see your post soon. Good Luck with the upcoming update. This article is really very interesting and effective.
I found your post interesting to read. I can’t wait to see your post soon. Good Luck with the upcoming update. This article is really very interesting and effective.
Good day, where can we get the files?