21. Dynamic Charts: Field Parameters, What-if and Dynamic Top N
21.5 Dynamic Top N with a Parameter
The Filters pane Top N (Module 22.4) uses a fixed N. To let users choose N with a slider:
Steps in Power BI
- Modeling › New parameter › Numeric range › Name
Top N, Whole number, 1 to 20, increment 1, default 5 › Create. - Create the measure below.
- Bar chart: Y-axis = Product Name, X-axis =
[Top N Sales]. Products outside the top N return BLANK and disappear from the chart. - 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 interview madhe scenario mhanun khup yeto. Ha measure swatah lihun bagha.
Ravindra Bagale's Tip
Mitrano, khup 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. Practice kara, mag ekdum sope vatel.