In the world of data visualisation, there is a constant tug-of-war between providing a high-level summary and giving users the granular detail they actually need. Often, we settle for a static 'Top 10' and leave it at that. However, this Workout Wednesday challenge pushed me to take a more user-centric approach: building a Dynamic Top N Report.
My goal for this build was to understand the measures needed for this function in PowerBI and make a visual improvement to the original WoW.
The Challenge at a Glance
The core of this task involved using Numeric Range Parameters to control a bar chart showing products by their total sales values.
My Strategic Approach:
To tackle this, I broke my workflow into three layers.
The Parameter: Setting up the parameter slicer to allow for a range of 1 to 10.
The Logic: Writing the DAX to rank products dynamically, ensuring that the highlight follows the users choice of Top 3, Top 5, or Top 10.
The Visual: Using conditional formatting and dynamic titles so that the report tells the user exactly what they are looking at, rather than making them guess.
My Step-by-Step Build
The chart is reasonably simple: Products by Total Sales. It does, what a bar chart does best, giving you a clear visual of which products have driven the highest sales volume relative to the rest. While that is already insightful the aim of this Workout Wednesday was to guide the users eye even more and to highlight a dynamic Top N.
Parameter
The first step was to create a Parameter which we will later use as out slicer. We want the option of Top 3, Top 5, and Top 10.
I configured a numeric range parameter with a minimum of 3 and a maximum of 10, with increments of 1, and a default of 3.
This gives us a slicer with the values 3 to 10, so not yet what we need.
I went into the table view to create a new column within the parameter table adding the word "Top " to the parameter values. Here we also want to make sure to sort this column by the "TopN Parameter" column to make sure the buttons will be displayed in the correct order.
We are getting closer. To limit our parameter options to only Top 3, Top 5, and Top 10, we want to click on the slicer on our report and go into the filter settings.
We can select to only show the options we want here.
Great our slicer is set up, now we need to make sure the chart works how we want it to work.
The DAX measures
First of all, a standard measure of:
Then our Rank. To be able to show the Top N products, we need to rank them.
We are saying: "Compare the Total Sales of the current product against the Total Sales of all products in the table, then assign a rank where the highest seller is number one."
Next we want to write a measure to colour our bars.
We are saying "Create a variable (like a measure within this measure) which is always our selected parameter value. Create another variable which defines a colour, based on whether the product rank is smaller or equal to the selected parameter value. And then return the defined colour."
And lastly we want insightful information in the subtitle, namely which TopN is highlighted, how much those products generate in sales, and what percentage that is of the sales of all products.
We are saying: "Check the TopN slider selection (defaulting to 3 if none is picked), calculate the sum of sales for only those top-ranked products, and determine what percentage of our overall sales they represent; then, display a sentence that says: 'Top X Products represents £Y in Sales, which is Z% of our Total Sales."
Now we just need to build.
The build
The slicer is already on the page, we just want to change that to a button slicer and change the value to Parameter Name (if we haven't done so already).
I did the following configurations:
Visual → Slicer Settings → Single select: on
Visual → Callout → Value → Horizontal alignment: centre
Visual → Buttons→ Apply settings to: All → Shape: Rounded rectangle | Corner radius 20px → Background: On → Border: On | Colour: 12239E
Visual → Buttons→ Apply settings to: Selected → Background: On | Colour: 12239E
General → Title: Off
For the chart, I chose a clustered bar chart and dragged Products onto the y-axis and Total Sales onto the x-axis.
I changed the Title to "Top Performing Products by Sales" and added a subtitle using the fx button and my TopN Subtitle measure. I coloured the subtitle in the same blue as the slicer.
I then also coloured the bars with the Bar Colour measure.
I also gave the chart a light blue background to match the blue colour scheme.
That's it. It took me a while to fully figure out the measures but once I understood them the rest was smooth sailing.