When working on budgeting analysis inside of Power BI, there may be situations in your work where you want to segment the results into different groups, so that you can analyse which performers were better than others, versus their targets for example.
This is some very unique analytics that can be done inside of Power BI in a relatively seamless way if you use the correct formula techniques.
We work through how you should set up your data model when adding a supporting table to your models with the unique grouping that you require.
We also then have to make sure that the grouping table works with the rest of the tables in your model. The DAX calculation logic and formula combinations you use are also extremely important to make this all work well.
Once we get the raw results versus our budgets or forecasts, we need to realise that more is possible. That’s were the dynamic grouping formula technique really comes into its own when using DAX.
Building up your calculations like this is very common when doing any sort of dynamic grouping. You may need to explore measure branching techniques here if this isn’t making total sense right at the moment.
Here though, we have a unique case where we branch out into our dynamic grouping calculation but prior to this, we have to generate some relatively complex analysis when comparing our actual results to our budget results initially.
We could be working across performance results of our customers, products, stores or regions. It doesn’t actually matter so much with this example. The formula techniques and model set up we need doesn’t actually change at all. It’s all the same, that’s why this overall analysis idea is so compelling.
It’s amazing what you can achieve with Power BI when looking for these types of insights.
Enjoy working through this detailed video.
Insert your email address and press Download for access to the files used in this article.
***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI
FREE COURSE – Ultimate Beginners Guide To DAX
FREE – 60 Page DAX Reference Guide Download
FREE – Power BI Resources
Enterprise DNA Membership
Enterprise DNA Online