In this tutorial, you’ll learn about the comparison of values between Top N and Average Of Others in Power BI. This analysis technique helps add directionality into your solution by seeing how much the Top N and Bottom N customers are above the average of the remaining customers. You may watch the full video of this tutorial at the bottom of this blog.
Setting Up The Model For The Comparison Of Values
This is the report that will be recreated in this tutorial:
You can see that if you change the context through the slicer, the customers and numbers change.
This is a simple Sales based model with the fact table and dimensions table. You can see its data model below:
It has a standard waterfall layout with the Dates, Customers, and Products dimension tables on the top and the Sales fact table below.
All relationships are one-to-many with the dimensions on the “one” side and the fact table on the “many” side.
Recreating The Sample Report
With the data set, you’ll need to add a couple of additional tables to use a slicer for the Top N and direction selection.
Creating Tables and Slicers
First, go to the Modeling toolbar and add a new parameter. Name the parameter Top N and set the data type to Whole Number.
Input 1 and 10 in the Minimum and Maximum parameters. Then, input 1 and 5 in the Increment and Default parameters.
Next, create a Directions table with values of direction for top and bottom, and a sort column. Go to the Home toolbar and click Enter Data.
Drag the Date column, Top N parameter, and Direction column to the canvas and turn them into a slicer.
Next, add a calculated table to add an Others row to the list of all customers.
To display the ranked values, add a table to the canvas. Drag Customer from the Customers Plus Others table in the table inside the dashboard.
Building Measures For The Comparison Of Values
Change the title of the table with the Customer Table Title measure.
Turn the Title off, and click the button in the Title Text below.
Select Field Value for the Format By parameter. Next, Select Customer Table Title inside the Display Measures for the Based on field parameter.
The Customer Table Title measure harvests the direction and the Top N slicers, and combines them with static text to generate a result.
Next, calculate the appropriate ranking value. To do this, you need both the normal top down descending ranking plus the bottom up ascending ranking.
This is the descending ranking:
And this is the ascending ranking:
Use the Top N and Direction slicers to return the appropriate customer ranking. Here’s an example of how it should look from the Customer Rank measure:
The function harvests the values from the two slicers and uses them to figure out what ranking to display.
Now, check if the ranking measures correspond to the slicer selections by placing the measures in the table.
If you change the context with the slicer selections, the values inside the table should also change accordingly.
After checking the measures, remove them except the Customer Rank measure and add the Customer Sales measure.
- 1st case: If the customer is ranked in the Top N, it will use the Sales value.
- 2nd case: If the customer name is Others, it will use the average of the Sales value for all non-ranked customers.
- 3rd case: If the customer is not ranked in the Top N, it will return as blank.
Creating A Stacked Column Chart
Copy and paste the table and turn it into a line and stacked column chart. Remove the Customer Rank measure; you won’t need it.
Next, add the Average Of Other Customer Sales measure into the line values.
Once done, go to Filters and filter out Customer Sales that are not blank and then click Apply Filter.
Lastly, similar with the Customer Table Title measure, change the title of the visual with the Customer Chart Title measure.
This measure harvests the selections of the Top N and Direction slicers and combine them with static text to form the title.
From there, you’ll notice that the table says “and” while the chart says “versus”.
Making The Visuals Stand Out
The next step is to color the columns.
The Customer Chart Colours measure assigns a grey color if the customer name is Others. Otherwise, it will apply a blue color.
Go to Data Colors and select button in the Default Color.
Select Field Value for the Format By parameter and select Customer Chart Colours inside the Display Measures for the Based On Field.
Creating The Final Measures
Now, use math and measure branching to create measures for the difference of the Customer Sales from the Average Of Others Sales.
This measure uses subtraction to get the Sales volume.
While this measure uses division to get the percentage value.
Add the measures into the Tooltips of the chart visual.
After that, when you hover over a customer, you can see how much their Sales were worth and how they compare from the average amount.
These visuals are responsive to the slicers in your canvas. You can utilize them to get data from different time periods and directions.
This tutorial shows the comparison of values between the Top N and Average Of Others values in Power BI.
The difference of the values is essential in monitoring the performance of your products. It helps you create more strategies to improve your sales. You may use this technique to improve and accomplish the sales goals in your organization.
***** Related Support Forum Posts *****
Ranking Members Within Group
Return Name Only For Max And Min Or Top N Average Value
Slicer From A Measure Like Top N
For more top n support queries to review see here….