How to Apply a Top N Filter Using Field Parameters and Measures in Power BI

Step 1. Creating a Field Parameter for Top N Values

To begin, we need to create a Field Parameter for the Top N filter. A field parameter allows you to dynamically switch between different values (e.g., showing the top 5 or top 10 results).

Steps to Create a Field Parameter:

• In Power BI, go to the Modeling tab.
• Click New Parameter > Field Parameter > Numeric Range.
• In the dialog box, set the following:
• Field Parameter Name: Top N
• Data Type: Whole Number
• Minimum Value: 5
• Maximum Value: 20
• Increment: 5
• Default: 5
• This creates a numeric parameter where users can select the top N values, ranging from 5 to 20 with increments of 5.

Step 2. Creating Measures for Total Sales and Total Quantity

Next, we create two basic measures: Total Sales and Total Quantity. These will be the KPIs that the user can select and analyze.

Measures for Sales and Quantity:

Total Sales:

Total Quantity:

These measures will serve as the foundation for switching between KPIs in the visual.

Step 4: Creating a Field Parameter for Field Selector

This parameter allows users to choose which field or dimension they want to analyze (e.g., Category, City, Segment). You can create a Field Parameter called Field Selector with the following fields:

• Category
• City
• Segment
• Ship Mode
• Sub-Category

Steps to Create the Field Selector Parameter:

1. Go to Modeling > New Parameter > Field Parameter.
2. Add fields like Category, City, Segment, Ship Mode, and Sub-Category to the parameter.

Step 5. Creating the KPI Column

Now, we create a calculated column in "KPI Field Paramter" to store the selected KPI value from the KPIs field parameter. This will help in dynamically displaying either Sales or Quantity.

Calculated Column for KPI:

This column simply reflects the value selected from the KPIs field parameter, which can be either Sales or Quantity.

Step 6. Creating the Switch Filter Measure

Now we need to create a measure to switch between the KPIs based on the selected field in the KPIs parameter.

Switch Filter Measure:

The SWITCH function checks the selected value of KPIs[KPI column] and returns either the Total Quantity or Total Sales measure based on the selection. If no valid selection is made, it returns BLANK().

Step 7. Creating the Rank Measure

We need to rank the selected data based on the field chosen in the Field Selector (Category, City, etc.). The rank will be calculated using the RANKX function.

Rank Measure:

The SWITCH(TRUE()) function ensures that multiple conditions can be evaluated. The condition checks which field (e.g., Category, City, etc.) is selected in the Field Selector.

The RANKX function ranks the data for each selected field based on the selected KPI (Sales or Quantity). We use ALLSELECTED to rank across the selected data.

Step 8. Creating the Top N Measure

We now create a measure that filters the top N based on the selected rank.

Top N Measure:

This measure checks if the rank is less than or equal to the selected Top N value. If true, it returns 1 (showing the item), otherwise, it returns 0.

Step 9. Building the Visual

Finally, we create a donut chart to display the results.

Steps to Build the Donut Chart:

• Add the Switch filter KPIs measure to the Values of the donut chart.
• Add the Field Selector parameter to the Legend.
• Add the Top N measure as a Visual Level Filter and set it to is 1.

These steps will help you visualize the selected KPI, filtered by the Field Selector and Top N measure.

Step 10. Using the Slicers

In this step, we add slicers to enhance the interactivity of the report. These slicers will allow users to filter and interact with the data more effectively.

Steps to Add Slicers:

• KPIs: Select between Sales and Quantity.
• Field Selector: Choose between Category, City, Segment, Ship Mode, or Sub-Category.
• Top N: Choose the number of top records to display (e.g., Top 5, Top 10).

These slicers will provide dynamic control over the data displayed in your visual, allowing for more customized analysis.