Ravindra BagaleCourses & study guides मराठी Track your progress

21. Dynamic Charts: Field Parameters, What-if and Dynamic Top N

21.5 Dynamic Top N with a Parameter

By Last updated:

Start with a problem

Shraddha wants a chart of the Top N stores where she picks N (3, 4 or 5) herself.

Explain simply

Combine a numeric what-if parameter Top N with a measure that ranks stores and shows sales only for ranks ≤ N.

Show the data

Store City Total Sales (₹)
Kothrud Pune 650
College Road Nashik 500
Rajarampuri Kolhapur 450
Gangapur Nashik 300
Baner Pune 200

About this data: This table uses columns: Store, City, Total Sales (₹). This table uses columns: Rank, Store, Top N Sales (N = 3). This table uses columns: Store, Total Sales, Rank. This table uses columns: Store, Total Sales. Values shown are the lesson examples already on this page (not a new dataset).

Fictional sample data. Practice file: visuals_orders_apr_may_2026.csv (source: src/practice/).

Demonstrate

  1. New parameter › Numeric range Top N: 1 to 5, step 1, default 3.
  2. Create Top N Sales = VAR n = [Top N Value] VAR r = RANKX(ALLSELECTED(Orders[Store]), [Total Sales]) RETURN IF(r <= n, [Total Sales]).
  3. Bar chart: Y-axis Store, X-axis Top N Sales; sort descending.
  4. Set N = 3.

Show the result

Your result should look like this (fictional sample):

Rank Store Top N Sales (N = 3)
1 Kothrud ₹650
2 College Road ₹500
3 Rajarampuri ₹450
— Gangapur, Baner blank (hidden)

Explain what happened

RANKX ranks each store among the stores in the visual; IF returns blank above N, and visuals hide blank rows.

Explore further

  • An Others bar (Gangapur + Baner = ₹500) needs an extra table with an Others row.

Detailed reference for this lesson:

Dynamic Top N with a what-if parameter in Power BI with example — in 30 seconds

What: A what-if parameter gives the user a number slider. A Top N measure uses that number to keep only the top stores.

Why: The ops head sometimes wants the top 3 stores, sometimes the top 5, in the same visual.

Tiny example: Slider = 2 → only Andheri East (₹1,100) and Kothrud (₹600) stay in the chart.

First look at the data (before → after):

Dynamic Top N with a what-if parameter in Power BI with example: before and after tables, fictional Blinkit Maharashtra data

Same before / after tables as text

Before — Sales by store (fictional)

Store Total Sales Rank
Andheri East ₹1,100 1
Kothrud ₹600 2
College Road ₹300 3
Baner ₹200 4

Formula / click path

Modeling › New parameter › Numeric range: Top N = 1 to 10, default 3
Show Store = IF(RANKX(ALLSELECTED(DarkStore[Store Name]), [Total Sales]) <= [Top N Value], 1, 0)
Filters on this visual: Show Store is 1

After — Slider = 2

Store Total Sales
Andheri East ₹1,100
Kothrud ₹600

Do it step by step:

  1. Click Modeling › New parameter › Numeric range.
  2. Name it Top N. Minimum 1, Maximum 10, Increment 1, Default 3. Click Create. A slider appears.
  3. Click New measure. Type the Show Store formula.
  4. Build a bar chart: Store Name and Total Sales.
  5. Drag Show Store into Filters on this visual. Set it to is 1. Click Apply filter.
  6. Move the slider to 2. Check: only Andheri East and Kothrud.

The Filters pane Top N (Module 22.4) uses a fixed N. To let users choose N with a slider:

Steps in Power BI

  1. Modeling › New parameter › Numeric range › Name Top N, Whole number, 1 to 20, increment 1, default 5 › Create.
  2. Create the measure below.
  3. Bar chart: Y-axis = Product Name, X-axis = [Top N Sales]. Products outside the top N return BLANK and disappear from the chart.
  4. Sort by Top N Sales descending. Add a dynamic title "Top " & [Top N Value] & " products by sales".
Top N Value = SELECTEDVALUE('Top N'[Top N], 5)

Top N Sales =
VAR n    = [Top N Value]
VAR rnk  = RANKX(ALLSELECTED(Product[Product Name]), [Total Sales], , DESC, Dense)
RETURN IF(HASONEVALUE(Product[Product Name]) && rnk <= n, [Total Sales])

Because of ALLSELECTED, the ranking respects slicers. For example, the top 5 in Nagpur on Amazon Now.

Ties

With Dense ranking, two products with equal sales share a rank, so you may see more than N bars. That is correct. Explain it in the subtitle, or add a tie-breaker.

Dynamic Top N comes up a lot as an interview scenario. Write this measure yourself.

Ravindra Bagale's Tip

Friends, many students build Top N with RANKX but forget ties, so "Top 5" shows 6 products. Decide how ties should behave (show them, or break them with a second measure), and tell users what happens. Test with a City filter to make sure the ranking respects slicers. Practise, and it will feel very easy.

Fix common mistakes

Wrong result Reason Correction
All ranks = 1 RANKX over a column not in the visual Rank over ALLSELECTED of the axis column
Ties make 4 bars for N = 3 Equal values share a rank Add a tie-breaker
Top N ignores slicers Used ALL instead of ALLSELECTED Use ALLSELECTED

Practise independently

Practice task

Set N = 4. Which store is added?

Hints
  • Next value after 450.
Solution

Gangapur ₹300.

Think like a professional

Dynamic Top N is a classic interview scenario. Explain ALLSELECTED vs ALL in your answer.

Loading DAX practice tasks...

  1. Product rank (Easy): Shown per Product Name. Measure: Product Rank = rank of [Total Sales] among all product names.
  2. City rank (Easy): Shown per Store City. Measure: City Rank = rank of [Total Sales] among all cities.
  3. Platform rank by orders (Easy): Shown per Platform. Measure: App Rank = rank of [Total Orders] among all platforms.
  4. Top 3 products only (Medium): Shown per Product Name. Measure: Top 3 Sales = [Total Sales] when the product rank is 3 or better, else blank.
  5. Top 2 cities only (Medium): Shown per Store City. Measure: Top 2 Sales = [Total Sales] when the city rank is 2 or better, else blank.
  6. City rank by units (Medium): Shown per Store City. Measure: Qty Rank = rank of [Total Qty] among all cities.
  7. Top 5 or others (Medium): Shown per Customer Name. Measure: Group = "Top 5" when the customer rank by [Total Sales] is 5 or better, else "Others".
  8. Sales of the top 3 products (Hard): Measure: Top 3 Total = [Total Sales] of the 3 best product names.
  9. Top 2 store share (Hard): Measure: Top 2 Share = sales of the 2 best store names ÷ sales of all stores.
  10. Dense rank of categories (Hard): Shown per Category. Measure: Cat Rank = dense rank of [Total Qty] among all categories, highest first.

See all Power BI practice tasks

Ravi Sir's Cards for this lesson

Dynamic Top N with a parameter. Users want to decide how many top items to see.Revision card

मराठी cards