Power BI Top 10 Products per Year (DAX)

Key insights

• DAX
  Formula language used in Power BI, Analysis Services, and Excel to build measures and calculations. Prepare a clean model with proper date and product keys and create base sales measures before ranking.
  
• TOPN
  Returns the top N products by a measure (for example, sales) and works well for yearly top 10 lists. Pair it with a summarized table to get the top products per year dynamically.
  
• SUMMARIZE
  Groups data by product and year and computes totals, which feed ranking functions like TOPN or RANKX. Use it to produce a concise table of product-by-year sales for further filtering.
  
• RANKX
  Assigns a rank to each product inside a filter context and helps when you need position numbers instead of row sets. Use it in measures to show or filter products that rank inside the top 10 for a given year.
  
• GENERATE
  Iterates across years or other dimensions to combine yearly top lists into one table or measure. Use it to collect yearly top 10 results, then count appearances per product for trend analysis and debugging logic step by step.
  
• evergreen products
  Identify products that appear in the top 10 across multiple years by counting yearly appearances and applying a threshold (for example, 3 of 5 years). These measures reveal consistent best-sellers and help guide inventory, pricing, and marketing decisions.
  

The recent YouTube video by SQLBI demonstrates practical methods to find the top 10 products by sales for each year using DAX in Power BI. The presentation focuses on producing dynamic, context-aware lists that adapt when users apply slicers or change visual filters. Moreover, it highlights techniques to identify evergreen products — those that consistently appear in the top 10 across multiple years — which is useful for strategic planning. Consequently, this summary breaks down the video’s core ideas, technical approach, and practical tradeoffs for report authors and analysts.

What the Video Explains

The video begins by explaining why the top 10 pattern is common but challenging when repeated over several years. First, the speaker shows how simple ranking in a single year differs from identifying products that repeatedly rank in the top tier. Then, the video introduces the need for iterative logic that can traverse each year and count how often a product appears in the yearly top 10. As a result, viewers gain a clear sense of both the business question and the technical steps required to answer it.

Key Techniques and Functions

To implement the solution, the author uses core DAX functions such as TOPN, SUMMARIZE, and RANKX, and then extends the logic with GENERATE and context functions like CALCULATE and FILTER. Initially, the workflow builds a summary table of sales by product and year, and then it applies TOPN to select the highest performers for each year. Subsequently, the video demonstrates how GENERATE helps iterate over year rows to assemble a combined view that supports frequency counts across years. Therefore, the approach combines summarization, ranking, and iteration to produce a reusable pattern.

In addition, the presenter recommends creating intermediate queries or measures for debugging so authors can validate each step before embedding it into final measures. This iterative debugging approach reduces surprises and helps isolate issues related to filter context or ties in rankings. Moreover, modularizing logic into user-defined functions or well-named measures improves reuse and clarity when reports grow. Consequently, maintainability and transparency improve at the cost of some initial development time.

Tradeoffs and Performance Considerations

While the described methods are flexible, they come with tradeoffs between performance, clarity, and maintenance. For instance, constructing detailed summary tables and iterating with GENERATE can be CPU-intensive on large datasets, so authors must consider model size and query optimization. On the other hand, simpler measures using RANKX may run faster but can be harder to adapt when the requirement changes to counting frequency across several years. Thus, analysts must balance the need for dynamism against the cost of increased compute and complexity.

Another practical challenge is handling ties and missing data, which can distort which products appear in the top 10 in a given year. The video shows how to handle ties explicitly, but doing so adds logic and can change the business interpretation of the results. Also, slicers and cross-filtering affect the visible top 10, which is useful for interactivity but complicates comparisons across fixed timeframes. Therefore, report authors should document assumptions and provide controls so viewers understand how selections affect outputs.

Practical Recommendations for Report Authors

Based on the video, start by preparing a clean data model with reliable date and product keys and then build simple base measures for sales and quantities. After that, create and test intermediate tables or measures that summarize sales by product and year, and only then introduce ranking logic with TOPN or RANKX. Furthermore, the presenter encourages converting frequently used patterns into reusable functions or measures so teams can apply the same logic consistently across reports. Consequently, you trade development effort early for long-term clarity and reuse.

In addition, monitor performance by testing measures on realistic data volumes and use tools to profile slow queries. Where necessary, simplify logic or pre-aggregate data to keep interactive performance acceptable. Finally, communicate business rules such as how ties are handled and what constitutes an "evergreen" product so end users interpret the visuals correctly. These steps reduce confusion and make reports more actionable.

Why This Matters for Business Reporting

Ultimately, the ability to identify products that repeatedly rank in the top 10 helps organizations focus on enduring strengths rather than one-off successes. By combining ranking and frequency counting in DAX, analysts can shift from descriptive snapshots to trend-aware insights that support inventory planning, marketing focus, and supplier negotiations. However, teams must weigh the complexity and performance costs against the value of the insights, and then choose an implementation that matches their reporting needs and infrastructure. In short, the video by SQLBI offers a practical, repeatable pattern for finding long-term best-sellers while highlighting the tradeoffs every report author should consider.

Keywords

DAX top 10 products by year, Power BI DAX top N annually, Find top 10 products every year DAX, DAX rank top products per year, Dynamic top 10 per year DAX, DAX top N per year tutorial, Power BI top 10 products yearly DAX, DAX calculate top 10 each year