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
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.
Ravindra Bagale's Tip – मराठी
मित्रांनो, बरेच students RANKX ने Top N बनवतात पण ties विसरतात, त्यामुळे "Top 5" मध्ये 6 products दिसतात. Ties कसे हाताळायचे ते ठरवा (दाखवायचे, की दुसऱ्या measure ने तोडायचे), आणि काय होतं ते users ना सांगा. Ranking slicers पाळतं ना याची खात्री करण्यासाठी City filter लावून test करा. Practice करा, मग एकदम सोपं वाटेल.
Ravindra Bagale's Tip – हिंदी
दोस्तों, बहुत से students RANKX से Top N बनाते हैं पर ties भूल जाते हैं, जिससे "Top 5" में 6 products दिखते हैं. तय करो कि ties का क्या करना है (दिखाना है, या दूसरे measure से तोड़ना है), और users को बताओ कि क्या होता है. Ranking slicers को मानती है यह पक्का करने के लिए City filter लगाकर test करो. Practice करो, फिर बहुत आसान लगेगा.