Introduction
Have you ever worked with or seen a pie chart with too many slices? When you get beyond 5 or so slices it starts to turn into a mess. In some cases we can solve this by grouping all of the small slices into a group. This works well if the majority of the pie is made up of the top 5 or so slices. However, sometimes your pie chart may interact with other visuals which means you don’t necessarily know how many slices make up the majority of the pie. This is where a Top N parameter comes in handy. I recently created this in Power BI for an Insurance client but it can apply to anyone.
Dynamic Top N vs Others
Client Request: We would like to see our top ‘N’ carriers versus one group containing all other carriers in regards to Gross Written Premiums (GWP).
Development: This is a Power BI function that needs multiple steps
1. Create a parameter table with the values to select from for the ‘N’ (1- 20 should suffice)- this will be used as the visual slicer
2. Create a Carriers table with an “Others” Row by using a Union on the existing carriers table to a new row with a value of “Other”
3. Create a relationship between the existing Carrier table and the new table with the Other row
4. Create the measure using DAX :
5. Set up visual using the Parameter in a slicer, the Top N metric, and the Carrier from the Carriers+ Others table created
Updated Request: Wow! Can we also see GWP in this way with State and Broker?
Development: Follow the above steps 2 more times, skipping step 1 since the parameter can be used in these cases as well.
Bonus Development: Find a way to add a dynamic title with a metric inside the title
- Create a title metric
2. Add this metric to the title formula
3. Voila!
