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

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

Practice and Answer Key: Parameters and Top N

By Last updated:

Suppose we are building a store page for the Blinkit Maharashtra ops head. Five stores (fictional practice data):

Store City Total Sales (₹) Total Orders
Andheri East Mumbai 1,100 2
Kothrud Pune 600 3
Dharampeth Nagpur 450 3
College Road Nashik 300 1
Baner Pune 200 2

Total: ₹2,650 and 11 orders.

Exercise (one under the other):

  1. Make a field parameter Metric with Total Sales and Total Orders. Single-select slicer.
  2. Make a numeric range parameter Top N from 1 to 5, default 3.
  3. Write Show Store (rank on Total Sales ≤ Top N) and use it as a visual filter on a bar chart (Y-axis = Metric).
  4. Make a numeric range parameter Discount % from 0 to 0.20, step 0.05.
  5. Write Sales After Discount = Total Sales × (1 − Discount %).
  6. Set Top N = 3, Metric = Sales, Discount % = 0.10. Then switch Metric to Orders.

Expected result (check your numbers):

Setting Bar chart shows
Top N = 3, Metric = Sales Andheri East ₹1,100 · Kothrud ₹600 · Dharampeth ₹450
Top N = 3, Metric = Orders Andheri East 2 · Kothrud 3 · Dharampeth 3 (same stores: the rank uses Sales)
Top N = 2, Metric = Sales Andheri East ₹1,100 · Kothrud ₹600
Card: Sales After Discount at 0.10, no filter ₹2,385 (2,650 × 0.9)
Card: Sales After Discount at 0.10, Andheri East ₹990
Answer key (open after you try) 
Top N                = GENERATESERIES(1, 5, 1)
Top N Value          = SELECTEDVALUE('Top N'[Top N], 3)
Show Store           = IF(RANKX(ALLSELECTED(DarkStore[Store Name]), [Total Sales]) <= [Top N Value], 1, 0)

Discount %           = GENERATESERIES(0, 0.20, 0.05)
Discount % Value     = SELECTEDVALUE('Discount %'[Discount %], 0)
Sales After Discount = [Total Sales] * (1 - [Discount % Value])

The field parameter table is made for you by Modeling › New parameter › Fields. Power BI also writes the Top N and Discount % tables and the "Value" measures when you use Numeric range.

Common mistakes:

What you see Why Fix
All 5 stores still show Show Store is not in Filters on this visual, or not set to "is 1" Drag it there and apply "is 1"
Every store has rank 1 RANKX over VALUES instead of ALLSELECTED Rank over ALLSELECTED(DarkStore[Store Name])
Two metrics show at once The Metric slicer allows multi-select Turn on Single select
Orders view shows a different top 3 than expected The rank is on Total Sales, not on Orders Rank on the chosen metric only if the brief asks for it

Samajla ka? The slider gives a number. The measure decides what to do with that number.

Thodkyaat sangaycha tar (quick recap)

Field parameter to switch measures, what-if for scenarios, and RANKX or the Top N filter for Top N. Use single select and check the sort. Now let us move on to filters and slicers.