Power BI is a business intelligence tool that is used to create interactive dashboards from raw and complex data and fetch useful information easily, and TOP N is a useful tool in Power BI. The TOP N function is a DAX function that is very useful for filtering and returning the top or bottom N rows based on conditions. In this blog, let us explore the TOP N function in detail.
Table of Contents
What is Power BI and TOPN?
Power BI is a friendly tool for data and business analytics developed by Microsoft that enables users to transform raw data into interactive dashboards. TOPN is a function in Power BI that allows you to identify the top items from a list or data quickly. It is a fast way of searching and fetching your data.
Why do we need to find the ‘Top’ Element?
Imagine you are running a business, knowing your top-selling products. Identifying your top customers allows you to create a strong relationship with them. Finding top regions helps you allocate resources effectively..
Tables and Measures
- In Power BI, your data is organized into tables, similar to spreadsheets. Each table has columns that hold different pieces of information. For Example:, Columns like Product name, Sales amount and region.
- Measures are custom calculations, you create to analyze your data. They summarize information and provide insights.
Master Power BI Today
Enroll now and transform your future
How TOPN Uses Data?
- TOPN takes a table as its data source and uses a measure or column to determine the ranking order. It then returns a new table containing only the top “N” items based on your criteria.
- Imagine you have a large data set with thousands of products, and you want to highlight the top 10 best sellers in it. Using TOPN, Power BI sorts your product data by sales figures and returns only the top 10 products, making it easier to analyze and visualize the “highest” performing items.
- TOPN DAX functions work well with DAX functions, which will allow you to solve more complex calculations.
The TOPN formula looks like:
TOPN(<n_value>, <table>, <order_by_expression>, [<order>])
Explanation:
- <n_value>: The number of Top items you want to see (eg, 3 for the top 3)
- <table>: Table containing your data
- <order_by_expression>: The column or measure you want to rank by.
- [<order>](Optional): DESC (Descending) from highest to lowest, ASC(Ascending) from lowest to highest .</pre>
Steps-by-Step Guide
- Select your data: Choose the table you want to use.
- Decide what you want to rank by: Pick a column you want to rank by.
- Choose how many “Top” items: Set n_value.
- Write the formula: Use the TOPN functions with correct arguments.
- Visualize the result: Create charts or tables for better decision-making.
Practical Examples
For a better understanding of the TOPN function, let’s understand how it works.
Example 1:
Let’s say you have a “Sales” table with “ProductID”, “ProductName”,” Amount”, and Quantity.
This is what the Sales Table looks like before executing the TOPN DAX Function.
Let’s execute the TOPN Function to find out the TOP 5 Products by Sales.
Top 5 Products by Sales =
TOPN(
5, -- Number of rows that have to be returned.
Sales, -- Replace ‘Table’ with your Actual Table
Sales[Amount], -- Replace Table[amount] with your actual column
DESC -- Sort from highest to lowest
)
Output:
Explanation: The TOPN(5, Sales, Sales[Amount], DESC) will return the top 5 products with the highest sales amount from the Sales table, ordered by the Amount column in descending order.
Example 2:
Let’s say you have a “Sales” table with “CustomerID”, “CustomerName”,” Amount”, and Year.
This is what the Sales Table looks like before executing the TOPN DAX Function.
Let’s execute the TOPN Function to find out the TOP 3 Customers by Revenue.
Top 3 Customers by Revenue =
TOPN(
3, -- Number of row to be returned(Top 3 rows)
Sales, -- Table name (Sales)
Sales[Amount], -- Column to sort by (Amount)
DESC -- Sort in descending order
)
Output:
Explanation:.The TOPN(3, Sales, Sales[Amount], DESC) will return the top 3 customers with the highest sales amount from the Sales table, ordered by the Amount column in descending order.
Get 100% Hike!
Master Most in Demand Skills Now!
Visualizing Your Results in Power BI
Once you’ve calculated your TOPN results in Power BI ,the next step is to visualize the data in a way that makes it easy to interpret. Power BI offers several types of graphs for visualization that can effectively display your TOPN data.
Here are a few common visualization types:
Stacked Bar Chart in Power BI
Let’s discuss the Steps needed to create a Stacked bar chart.
Step 1: Prepare your data set.
We will use the above-discussed data set.
We will calculate the TOP 3 customers by Sales amount and visualize them using a stacked bar chart.
Step 2: Create TOP 3 Customers by Sales Measure
Here is the DAX formula to create this measure:
Top 5 Products by Sales =
TOPN(
5, -- Return number of rows(top 5)
Sales, -- Replace ‘Table’ with your Actual Table
Sales[Amount], -- Replace Table[amount] with your actual column
DESC -- Sort in highest to lowest-
)
This will calculate the Top 5 customers based on their sales amount in descending order.
Step 3: Create a stacked bar chart.
From the visualization panel, click on the Stacked Bar Chart icon and set the axis by dragging CustomerName to Y-Axis and Amount to X-Axis.
This is how the visualization panel looks.
Step 4: Resulting visualization:
Use Case: A sales team wants to compare the sales performance of different regions over several quarters. A Stacked Bar Chart shows each quarter as a bar with segments representing sales from different regions.
Importance: The Stacked Bar Chart helps in visualizing the total value and its components
Pie Chart in Power BI
Let’s discuss the Steps that need to be taken to create a Pie chart.
Step 1: Prepare your data set
We will use the above-discussed data set.
We will calculate the TOP 3 customers by Sales amount and visualize them using a Pie chart.
Step 2: Create TOP 3 Customers by Sales Measure
Here is the DAX formula to create this measure:
Top 5 Products by Sales =
TOPN(
5, -- Return number of rows(top 5)
Sales, -- Replace ‘Table’ with your Actual Table
Sales[Amount], -- Replace Table[amount] with your actual column
DESC -- Sort from highest to lowest
)
This will calculate the Top 3 customers based on their sales amount in descending order.
Step 3: Create a Pie chart
From the visualization panel, click on the Pie Chart icon and set the Legends by dragging CustomerName and Amount to values.
This is what the visualization panel looks like.
Step 4: Resulting visualization:
Use Case: A retail company wants to visualize its sales distributions across different regions. The Pie Chart will represent the percentage of total sales generated from each region (eg, North America, Asia, Europe)
Importance: This helps businesses to quickly see which region is performing the best.
Donut Chart in Power BI
Let’s discuss the Steps that need to be taken to create a Donut chart.
Step 1: Prepare your data set
We will use the above-discussed data set.
We will calculate the TOP 3 customers by Sales amount and visualize them using a Donut chart.
Step 2: Create TOP 3 Customers by Sales Measure
Here is the DAX formula to create this measure:
Top 5 Products by Sales =
TOPN(
5, -- Return number of rows(top 5)
Sales, -- Replace ‘Table’ with your Actual Table
Sales[Amount], -- Replace Table[amount] with your actual column
DESC -- Sort from highest to lowest
)
This will calculate the Top 3 customers based on their sales amount in descending order.
Step 3: Create a Donut chart.
From the visualization panel, click on the Donut chart icon and set the Legends by dragging CustomerName and Amount to values.
This is what the visualization panel looks like.
Step 4: Resulting visualization:
Use Case: Intellipaat wants to show its sales distribution by Course Category(eg, Data Science, Digital Marketing).A donut Chart will show the percentage of total sales from the category in a clear and easy-to-read format.
Importance: A donut chart helps in understanding part of whole and their circular shape with a hollow centre makes it easier to compare proportions visually.
Stacked Column chart in Power BI
Let’s discuss the Steps you need to take to create a Stacked Column chart.
Step 1: Prepare your data set
We will use the above-discussed data set.
We will calculate the TOP 3 customers by Sales amount and visualize them using a Donut chart.
Step 2: Create TOP 3 Customers by Sales Measure
Here is the DAX formula to create this measure:
Top 5 Products by Sales =
TOPN(
5, --Number of Rows to be Returned
Sales, -- Replace “Table name” with your Actual Table name
Sales[Amount], -- Replace “Table[amount] with Actual Table
DESC -- Sort from highest to lowest
)
This will calculate the Top 3 customers based on their sales amount in descending order.
Step 3: Create a stacked Column chart.
From the visualization panel, click on the Stacked Bar Chart icon and set the axis by dragging CustomerName to Y-Axis and Amount to X-Axis.
This is what the visualization panel looks like.
Step 4: Resulting visualization:
Use Case: A Marketing team wants to track monthly revenue by product and region. A stacked column can display the total revenue for each month, with different segments showing how much each region contributed.
Importance: A Stacked column chart allows you to compare the total value while also breaking it down into different sub-categories, which helps understand both the overall and individual contribution.
Best Practices For Using TOPN
- Start with Simple Data and Calculation: Try to practice TOPN with small datasets just to get an idea about these functions and their working.
- Always Double-Check Your Result: Data Validations are crucial. After using TOPN on your dataset, check the result by visualizing it to confirm your result.
Conclusion
TOPN is a small data tool for Power BI that helps you find out the TOP N in the data. It is easy to use and has a variety of uses for analysis, visualizing that analysis, and making decisions on the analysis. By quickly displaying the best N in data, TOPN saves time and provides instant focus on the most important parts. TOPN is a tool to help make data-driven decision-making effective. It’s a useful add-on to help make reports memorable.
To learn more about Power BI and its functions, check out this Power BI Course and also explore Power BI Interview Questions prepared by industry experts.
TOPN DAX Function in Power BI – FAQs
Q1. What is n_value?
The n_value is the number of top items you want to see.
Q2. What is order_by_expression?
Order_by_expression is a column or measure you want to rank data by.
Q3. What does DESC mean?
DESC means Descending and used to sort your data from largest to smallest.
Q4. What if I have ties in my data?
TOPN might return more items than you specified.
Q5.What is the difference between DAX RankX and TOP N?
The primary difference between TopN and RankX is their purpose. The TopN function is used to return a set of items based on their ranking, while the RankX function is used to return the rank of each item in a table.