Power BI DAX Formula Technique: Showcasing Growth Performance Effectively

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.

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

Power BI Trend Analysis: Are Margins Expanding Or Contracting?

In this blog post, I will be diving into a relatively specific insight by conducting a Power BI trend...

Data Visualization Report Frameworks | Part 2

For today, we'll be continuing the discussion on our enhanced visualization frameworks for Power BI....

Create A Power BI Sparkline Chart In Report Builder

In this Power BI Report Builder tutorial, youâ€™ll learn how to add a sparkline chart in your paginated...

Using Iterating Functions SUMX And AVERAGEX In Power BI

One of the most crucial topics for any Power BI beginner to know about is iterating functions. You may...

PowerApps Documentation: Using MS Docs For Expert Functionalities

Let's talk about Microsoft's PowerApps documentation and what an important resource it can be for users...

Data Modeling In Power BI: Tips & Best Practices

In this tutorial, you'll learn valuable tips and best practices for data modeling in Power BI. You can...

Using The Query Builder Feature In DAX Studio

In this tutorial, youâ€™ll learn how to use the Query Builder feature to easily create queries and...

Time-Related Insights From Your Supply Chain Metric

For this tutorial, I'm going to cover some high-quality time-related insights directly from your supply...

Create A Multilingual Power BI Report

For today's blog, I want to discuss a not-unusual situation with many of my consulting assignments for...

CROSSJOIN DAX Function: Server Timings & Query Plan

In this tutorial, youâ€™ll learn how the CROSSJOIN function works in DAX using the Server Timings pane...

Overview Of The DAX Studio Keyword COLUMN

Another important keyword to learn when using DAX Studio is the COLUMN keyword. In simplest terms, the...

Python Cheat Sheet: Essential Quick and Easy Guide

When programming in Python, having a reliable cheat sheet by your side can be a game-changer. Python...