I’ve got an issue that I’m hoping someone can help solve. I want to do a bottoms-up price, volume, mix analysis. I know I can do a quick and dirty version via DAX measures. However, the answer is never as accurate as it needs to be. So, I’ve created summary tables for sales and budgets. For this reason, I cannot merge them in the query editor. They look like this.
How can I join them? I’ve tried Crossjoin and naturalinnerjoin, but I get an error. I’ve even tried doing it all in one big DAX calculation, but no luck. Worst of all is that I tried doing a lookup (from budget to sales) but that doesn’t work because I have some things budgeted that I haven’t sold and vice versa.
Any help would be appreciated as I’m out of ideas.
Can you upload some sample data along with a quick mockup of what you would like the output to be? Seems like this would be best to do in Power Query, but would like to see some data if possible.
I tried this and it combined my budget and actual values instead of keeping them separate. I’m not sure how to stop this from happening, so I’m going to try to learn more about UNION before I try anything else.
If I can’t make any progress I’ll upload a pbix file with dummy data and the model set up the same way I have mine so we can test things.
@wesleyfletcher,
From what I quickly gathered is that you have a granularity mis-match between your sales (which is at the daily level) and your budget which is at the monthly level. So a few things you can do for this:
Only list both sales and budget at the monthly level
Show values for daily and monthly level, though budget will only be at the first of the month, while sales will be shown daily
Show sales at the daily level and then allocate the monthly budget. Could be something like total monthly budget/ number of selling days in the month. Or based on last year sales, or based on some other metric
Since I see you have a budget date column in your sales table (which looks like corresponds to the first of the month) you can create an inactive relationship from Dates to Sales [Budget Date]
In doing that you dont need these summarized tables. Your Budget measures will stay the same but your actual measures would have to change to : Actual Volumes = CALCULATE(SUM( Sales[Volume] ), USERELATIONSHIP(Dates[Date],Sales[Budget Date]))
Yes by looking at the data, I’m now not sure why you’ve set things up this way. You don’t even need these summary calculated tables. They are totally unnecessary.
All you have to deal with here is data at a different granularity. Your model can be far simpler that what you are doing here.
I detail how to solve for this in many locations. All you need to do is work out the budget allocation methodology.
See below.
Lots of example in here
Some specific videos to review.
I think the best workshop on this topic is this one.
Also check out this one here if you have time.
These all cover how to setup your model and then how to structure you formula to allocate your budgets effectively.
This is what you need to solve here to get this right in your model.
Test Data.pbix (291.1 KB) Thank you for all of your help and direction. However, unless I’m missing something (it’s very likely that I am), I don’t think this helps me find the right result.
I’ve included a .pbix file with the correct answer that I calculated in excel. The table is PVM answer. The screenshot below highlights the difference between using measures and using calculated columns do to a price volume mix analysis.
The Top-Down Results aren’t accurate because they aren’t calculated at the lowest level. If I included all detail in a BI visual, then the measure result would be accurate. However, It doesn’t make sense to do this. I want the calculation to be accurate at any level.
I thought that if I were able to get my sales information and budget information at the same detail level, then joining the tables would be easier. From there I would use calculated columns to finish the PVM analysis. The reason for this thought is based on my understanding of CONTEXT.
Does this make sense? Once again, I may have missed something that you tried to communicate. If I have then I apologize.
I’m not sure that this is the most elegant solution. However, it is one that works.
I’ve attached the corrected file and I hope that it is helpful to someone. I know PVM analysis is meant to be directional, so 100% accuracy isn’t needed. However, in my industry it’s impossible to get a directional PVM analysis unless it’s done from the ground up (and using margin instead of revenue) due to commodity impacts on pricing and frequent product changes.
Thanks to the contributors of this post. Due to the length by which this post has been active, we are tagging it as Solved. For further questions related to this post, please make a new thread.
