DAX for Perfect Price Volume Mix (PVM) Calculation Power BI Tutorial
Vložit
- čas přidán 6. 08. 2024
- This is the third installment in the series; however, you are not required to read the previous two to follow along.
These are the formulas that I used in the video
PRICE IMPACT = Volume TY * (Price TY - Price LY)
MIX IMPACT = Units TY Mix Gain * (Price LY - AVG Price LY)
VOLUME IMPACT = (Vol TY - Vol LY) * Avg Price LY +
(Price LY - AVG Price LY) * (VoL @ LY Mix - Vol LY)
I have made a small adjustment to the Volume Impact to make sure that the logic nets perfectly both for every line item and total overall.
Since the files used in this tutorial are still work in progress, I will not be posting them here quite yet, but if you cannot wait, then you can register at knowledgebank.pro , accept the email invitation and then use Microsoft Teams to switch to KnowledgeBank.Pro tenant and grab the files from there.
Link to blog post: businessintelligist.com/2020/...
Thanks for sharing this awesome video.. could you make detailed videy on MARGIN PVM
Nice video, how do you change the the header color to have different color for last year, this year and impact?
Amazing video. Looking forward to the PowerBI model.
I have a question: for Gross Margin approach, can I apply the same calculations for COGS (sum volume impact in COGS with revenue, same for mix)?
effectively yes, but you should also consider if it would be beneficial to break it into buckets and do each of them separately and then sum up, might show you how Price impact changed from Revenue, to Discounts/Allowances, to Net revenue all the way to GM
Do you have an example on how to calculate FX impact?
Very nice video. Just one question. I think this is a fixed mix analysis, meaning the mix effect an all the other calculations do not adapt to the filters you can put in the visualization mode (for ex. if I have some Sales channels and below it some brands, does the mix calculation adapt if i filter just for one or 2 channel?) . Am I right? If this is the case, do you know if it is possible to calculate a dynamic mix effect calculation?
Thanks!
yes, i made the DAX as simple as i could so it was not confusing for beginners, depending on the level of detail at which you will be calculating your PVM impacts you might have to add more robust filtering logic
Very interesting stuff. I have registered on the site, but did not receive an email for confirmation. How do I proceed to get access to the model?
The registration link if returning an error message which makes it impossible to register for the files. Could you please check and revert.
I have accepted your invitation for knowledge bank but do not see anything on my Teams. How can access your datasets and examples?
I seem not able to match the Matrix column header colors with those of the three text boxes, kindly assist
take a look at the video here and you can also register at knowledgebank.pro to get the actual file... czcams.com/video/te5kKFqrB8U/video.html
How did you d color coding?
Hello, I find this approach really helpful and would like to possibly look at the PBI model, is there a way I can have it via e-mail? Unfortunately I cannot use my company e-mail address to access your tenant. Thank you very much for your support.
Hi thanks for sharing. Your formulas in ppt are wrong, Mix Impact stands for Volume impact and visceversa.
Great material (y)
woops, i will need to fix that in the future videos, ty
@@KnowledgeBankPro Does this mean that --> MIX IMPACT (Volume = Units TY Mix Gain * (Price LY - AVG Price LY)
VOLUME IMPACT (Mix) = (Vol TY - Vol LY) * Avg Price LY +
(Price LY - AVG Price LY) * (VoL @ LY Mix - Vol LY) ?????/
* MIX IMPACT (Volume) = Units TY Mix Gain * (Price LY - AVG Price LY)
Great, I have registered on your blog but I still have no access to content. I get the message "Your are already registered, Close Window" However on clicking on Close Window it is non-responsive
you should go to teams.microsoft.com and then switch to knowledgebank tenant to see the content
Awesome video Andre, quesiton, I enrolled on the website and accepted the email, but I still cannot see the tenant my Teams, any reason why ?
Same here actually :/ is it possible to get the link by email or similar
@@martintibbie5511 it now appear, but it does´t connect to the Tenant of Knowledge bank pro.... any reason there Andre ? thanks!
looking into it, please give me a day or two to troubleshoot... it could be on your company side to disable this option but I am hoping to find a way with a direct link to knowledgebank.pro tenant...
Sorry, Martin... I am looking into this now, we should be able to find a way to make it work
i did some research, can you please try to open a private window in your browser, to to teams.microsoft.com and log in there? that seems to help, along as clearing out temp files and reinstalling teams
it seems if PVM is done at customer/product level, then the DAX used needs to be different.... SUMX returns wrong values :(
Nan error appearing while applying price variance. please help
i have more details on the calc in this video: czcams.com/video/te5kKFqrB8U/video.html