Budgeting performance analysis might sometimes require us to segment the results we currently have to different groups. This is to analyze which performers were better than others versus their targets. You may watch the full video of this tutorial at the bottom of this blog.
Power BI can manage performance segmentation, and understand their differences and effects on the total budget. In this tutorial, I’m going to discuss how to perform a budgeting performance segmentation.
Segmentation is a technique used to narrow down large target subjects into defined target groups.
This is a common requirement for budgeting type analysis. However, it’s not an easy technique for those who are just starting out with Power BI. There’s a lot of things that you need to get right for this technique.
I’ll also discuss how you can segment the budgeting dimension that you’re analyzing. You can segment into groups like best performers versus worst performers. This can be based on the difference between your revenue and budget.
The main thing to note here is to understand the formula for data segmentation.
Supporting Table For Budgeting Performance Segmentation
We’ll be using this report page that I used during the last Enterprise DNA summit. The sessions were all about budgeting.
However, this tutorial is just going to focus on how to segment data into groups. This will help us see how to create groups that did not exist within our data, but were derived from the results based on our data.
All of the budgeting measures here were already derived since they were discussed during this Learning Summit. You can check out all the resources and the discussions that you can download.
Let’s say that we already have the performance of our sales to the budget data. Then, we need to segment this to identify who is positively performing from the group, or in this example, region.
But we have to remember that this doesn’t exist in our data set. Hence, it is imperative that we create a supporting table to categorize the performance per region. Let’s take a look at this existing supporting table.
Based on this table, the regions are classified as Best Performers if they are 40% above budget. If they are below their budget and not meeting them at all, then they are classified as Worst Performers.
This will allow us to get better insights and understand the scenario behind our analysis. The data per segmentation will be reflected after creating the supporting table.
Understanding The Budgeting Performance Segmentation Formula
The image below shows the commonly used general DAX pattern for data segmentation.
Since we are looking for data from different regions, it is essential to iterate through every single city inside each region. And that’s what the RankingDimension variable does. It’s creating a list of every single city name.
Then, we’ll put that inside the FILTER function because we need to evaluate every single city based on their Sales vs Budget percentage. After that, we’ll use the result from that evaluation to identify the budget range or group a city belongs to.
As you can see here, the previously discussed calculation counted up the number of cities in each of the Performance Groups that we created.
We can also use this similar pattern for the Budget Group Sales measure.
Using The Same Formula For The Budget Group Sales Measure
First, let’s click the Budget Group Sales Measure and check out this formula:
Here, we used a logic that is almost the same as segmenting the stores per city. There’s not much change from the pattern. But, instead of counting the stores, we’re calculating the Total Sales of each store using the CALCULATE function.
This information is useful if we’d like to show the results using visualizations such as a map. This will color each city depending on which performance group they belong to in the selected context.
We can click on this visualization to check out the cities that belong to a certain performance group. For this example, let’s click the Best Performers group.
When we check out the map and take a look at the Miami region, we’ll see that it’s a big city. That may be the reason why sales are better in that region.
When we click on the Worst Performers group, we can also see the cities that are included in that performance group.
Dynamic Grouping Based On The Selected Segment
Another thing to note here is that we need to recreate all the corresponding measures so that they can change based on the selection from the performance group visualization. These are the Budget Group % Difference, Budget Group Sales, Budget Group Performance, and the Budget Group Counts measures.
This is because we are selecting a dimension inside a supporting table with no relationship to our core model.
Once we use our old measures and select from the performance group measures, it won’t affect the other calculations (Budget Group % Difference, Budget Group Sales, Budget Group Performance, Budget Group Counts) that we are displaying.
What makes the Budgeting Performance Segmentation measure dynamic is that it changes the other calculations based on the selected data from the performance group visualization. To do this, we need to change all our calculations to a different context. When a selection is made, we need to recalculate it based on this segmentation logic.
Finally, this part of the logic will iterate through the sales of every region and identify the performance group a city belongs to. And that’s mostly what you need to successfully segment your data or results into groups.
***** Related Links*****
Advanced Product Budgeting Analysis – Power BI & DAX Techniques
Budgeting Analysis On Best & Worst Performers Using Power BI
Customer Segmentation Techniques Using The Data Model – Power BI & DAX
Conclusion
To summarize, Budgeting Performance Segmentation using DAX in Power BI can be beneficial in various ways. It is a great way to present data over time. It can open the discussion across the performance results of customers, products, stores, or even regions.
Not only does it allow you to group data based on a specified segmentation, it also makes it easier to manipulate the raw results and compare them with the budgets or make forecasts.
Although the discussed dynamic segmentation is seamless, one must be able to generate relatively complex analysis when comparing actual results to budget results.
Hopefully, you got a lot out of this technique and found ways on how you can implement it in your own models.
Sam
[youtube https://www.youtube.com/watch?v=8YyZ6Wt8dpI&t=2s&w=784&h=441]
