TOPN() Function in Power BI: Step-by-Step Guide to Calculating Top 10 Stores
Table of Contents
Introduction
Analyzing your top-performing products is one of the most valuable insights you can extract from any Power BI model. Whether you want to understand which products contribute most to your revenue or highlight high-value items in your reports, the DAX function TOPN() in Power BI makes this analysis simple and effective.
In this blog, you will learn how to use TOPN() in DAX Studio to calculate the products by Sales, step by step. We will build a table expression using SUMMARIZE(), run the query inside DAX Studio, and then bring the logic into Power BI as a calculated table and measure. This hands-on exercise helps you understand not just how TOPN works, but also how DAX Studio can speed up your analysis, validation, and debugging workflows.
To master advanced DAX functions like TOPN() and SUMMARIZE(), it helps to have a solid foundation. Start with our Power BI DAX Course.
Top 10 Stores’ Contribution to Sales
In this blog, you will learn how to use TOPN() to find the top 10 stores by sales in DAX Studio.
TOPN() needs a table or a table expression to iterate row by row and calculate the top rows. We will use SUMMARIZE() to create a table expression for store-wise sales amount. The DAX function SUMMARIZE() aggregates and groups data by leveraging existing relationships in your data model.
To find the Top 10 Stores’ contribution to Sales:
- Download and open the .pbixfile in Power BI Desktop.
- You can get the sample file from Microsoft here:
https://learn.microsoft.com/en-us/power-bi/create-reports/sample-store-sales - Alternatively, you can download the Power BI and DAX file from the link provided below.
- Open the .pbix file in Power BI Desktop.
- Click on the ExternalTools tab in the Ribbon.
- Click on DAX Studio.
Please close the query builder if it is already open.
- Erase the existing code in the Query Pan.
- In the Query Pan, type the following code.
- Click on Run in the File Tab.
The query generated in DAX Studio is always a table.
To create a calculated table in Power BI:
- Copy the codefrom lines 2-10 in DAX Studio.
- Open Power BI Desktop
- Click on the Modeling tab in the Ribbon.
- Click on New Table.
- Paste the code in the formula bar and press Enter.
Let’s create a calculated measure using an iterator so that data can be further sliced and diced.
- Right-clickon Testing DAX Table in the Fields.
- Click on New measure.
Measure tools open.
- Type the following formula in the formula bar and press Enter.
- Click on the “$” and type 2 in the box.
- Click on the matrix visual in the visualization pane.
- Drag and drop Category from the Products table to the rows.
- Drag and drop TotalSalesLY from All Measures to the Values
- Drag and drop Top 10 Stores by Sales from Testing DAX to the Values.
Conclusion
From writing and testing DAX queries to analyzing model performance with VertiPaq Analyzer, it simplifies the complex and speeds up your workflow. You can also use functions like TOPN() in Power BI to easily identify and rank the highest-performing items, such as top-selling stores or products, directly within your queries. If you want to grow your DAX skills and unlock maximum performance in Power BI, DAX Studio is one tool you should always keep in your toolkit.
FAQs
Yes, DAX Studio is completely free and open source.
Yes, you can connect it through the External Tools ribbon or by launching DAX Studio and selecting your open Power BI file.
It helps write DAX queries, analyze performance, export query results, visualize table outputs, and debug slow models.
Further Learning and Resources
For more detailed learning and advanced Power BI training, go to
- Microsoft 365 Copilot Training Courses – Practical Learning for Modern Work
- Power BI Advanced Training Course.
- Power BI Report Design
- Power BI Essentials
- Power BI DAX Essentials
- Power BI Service Course
We offer comprehensive courses across major regions, including:
Downloads
