How to Create a Dynamic Top N with Toggle for 'Others' in Power BI to Enhance Data Visualization

In this comprehensive guide, we will walk through the process of creating a dynamic Top N visualization in Power BI that includes a toggle button allowing users to show or hide the 'Others' category. This feature enhances the interactivity of your reports and provides a clearer understanding of the data distribution.

Creating the Toggle Button

The first step is to create a toggle button that lets users decide whether they want to see the 'Others' category in the visualization.

Step 1: Add a Calculated Column

Navigate to your data table where the category axis is defined. Create a new calculated column named Select Others using the following logic:

Select Others = IF('Table'[Pharmacy Name] = "Others", 1, 2)

This column assigns a value of 1 to the 'Others' category and 2 to all other categories. This will be used to control the slicer behavior.

Step 2: Set Up the Button Slicer

Use the button slicer visual in Power BI and bind it to the Select Others column. Apply a filter on the slicer to only show the value 2 when the 'Others' category is hidden.

Step 3: Format the Toggle Button

• Disable the display of the slicer value.
• Upload images for the toggle states: one for toggle ON (showing 'Others') and one for toggle OFF (hiding 'Others').
• Assign these images to the respective states (Selected and Default) in the button slicer settings.
• Remove any background and border from the button for a clean look.
• Optionally, set the hover state image to the same as the default to avoid blank appearances on hover.

Step 4: Add a Title to the Toggle

Add a descriptive title such as "Show Only Top N" or "Hide Others" to inform users about the toggle's function.

Adjusting the Top N Slicer

Rename your Top N slicer to something more intuitive like Select Top N to improve user experience. Adjust the font size and layout to fit your report design.

Creating the Dynamic Subtitle Measure

The dynamic subtitle will update based on the Top N selection and the toggle state, showing relevant summary information.

Step 1: Define Variables

Create a new measure called Subtitle and define the following variables:

• TopEndValue: captures the number selected in the Top N slicer.
• CategoryCount: reads the toggle slicer value to determine if 'Others' are shown or hidden.
• AllPharmacies: a summarized table of all pharmacies with their total revenue.
• TopPharmacies: a table of the top N pharmacies based on revenue.
• OthersTable: a table of pharmacies not in the top N.
• TotalRevenue: total revenue across all pharmacies, ignoring filters.
• TopEndRevenue: total revenue for the top N pharmacies.
• OtherRevenue: total revenue for the 'Others' category.
• TopEndPercent: percentage contribution of the top N pharmacies to total revenue.
• OtherPercent: percentage contribution of the 'Others' category.
• OthersCount: count of pharmacies in the 'Others' category.

Step 2: Build the Return Statement

Use an IF statement to check the toggle state:

• If CategoryCount equals 2 (meaning 'Others' are hidden), return a string showing the top N selection, total revenue, and percentage contribution.
• Otherwise, return a string that includes the above information plus details about the 'Others' category, including count, total revenue, and percentage.

Format the numbers appropriately for currency and percentages.

Applying the Dynamic Subtitle to the Report

• Enable the title on your chart.
• Use conditional formatting for the title and bind it to the Subtitle measure.

Now, when toggling the button:

• If toggled ON to hide 'Others', the subtitle shows only the top N pharmacies' contribution.
• If toggled OFF to show 'Others', the subtitle includes detailed information about both the top N and the 'Others' category.

Changing the Top N slicer will dynamically update the subtitle and the chart accordingly.

Conclusion

This method provides a powerful way to enhance your Power BI reports by allowing users to dynamically control the visibility of the 'Others' category in Top N visualizations. The toggle button combined with a dynamic subtitle improves clarity and user engagement.

If you found this guide helpful, consider sharing it with others learning Power BI. Stay tuned for more tutorials to enhance your data visualization skills.