I want to share with you a Power BI DAX formula technique to show growth performance effectively. In this tutorial, I’m going to demonstrate how it’s done, but there are so many different scenarios where you want to represent growth and this is just one example. You may watch the full video of this tutorial at the bottom of this blog.
The technique that I’m going to go through can be replicated in many different scenarios, so don’t get too caught up in this specific one. I want you to focus on understanding and learning the Power BI DAX formula that you need to use.
In this example, I calculate the profits of a company, and I want to see the growth over time. I want to be able to represent that in a scatter chart that allows me to see the clustering of good-performing, average-performing, and poor-performing stores.
Moreover, I want it to be represented in a spatial way to identify any clusters in a particular region.
Measure Branching With Power BI DAX Formula
The key technique here is measure branching, which uses a core Power BI DAX formula to create more measures. In other words, we start with simple calculations, then we build calculations that represent those formulas.
This example is a demo resource that was from a Learning Summit in Enterprise DNA. In this case, I needed to work out the Profit Difference. As you can see, this formula is purely just referencing measures, Total Profits and Profits LY (last year).
The formula for Profits LY is also quite simple. It uses a time intelligence function, SAMEPERIODLASTYEAR.
These results in the visualizations will update depending on whatever time context I input.
Working Out Profit Growth Versus Last Year
Once I have the Profit Difference, I work out the Profit Growth versus Last Year. The formula simply calculates by subtracting Profits LY from Total Profits, and then divide it by the Profits made last year.
So if the profits this year are exactly the same as the profits made last year, there’s zero growth.
Using Supporting Tables
I didn’t want to only show all these results in just a scatter chart because it doesn’t really provide that much information. The results can be represented in a more effective way. This is where this dynamic grouping technique really comes in. I wanted to be able to visualize who were the best growth areas or states versus some of the lowest ones.
To achieve this, I utilized a supporting table to create these groupings.
I’ve created this by going to Enter Data; here I just input all of these figures (Min and Max) to represent the groups, High, Average, Low, and No Growth.
Pattern Recognition DAX Formulas
To get these groups represented on this visualization, I utilize a more advanced Power BI DAX formula. This formula breaks up the States based on the result. It works through each State with VALUES function. For each State, it works out the Profit Growth vs LY to see what group it is in.
If that particular State equates to true in a particular group, it gets associated to that particular group based on this visualization.
So this Performance Group actually comes from that supporting table that I created.
I did exactly the same with the other formula. The only difference is that this is representing Profit Difference.
I can change to any time frame here and it updates and maintains the groupings based on the outlines of the groups that I created in the supporting table.
***** Related Links *****
Use Power BI Measure Branching To Check If Your Margins Expand As Revenue Grows
Power BI Advanced Analytics: Secondary Table Logic Techniques
Use DAX To Segment & Group Data In Power BI
Conclusion
This technique around Power BI DAX formula is a really effective and dynamic way to visualize any growth metrics over different periods. I think consumers would love seeing data in this way.
I hope you got a lot out of this tutorial. Measure branching is quite advanced, but it’s such a great technique. I also demonstrated some segmentation and visualization techniques with DAX, so hopefully you got a lot out of it and utilize them in your own models.
Cheers!
Sam
***** 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
Enterprise DNA Events
[youtube https://www.youtube.com/watch?v=MqGzKEbjNiI?rel=0&w=784&h=441]
