Power BI: Top N Analysis


Top N analysis is a common business technique that involves sorting data to view a specific category’s top or bottom values. For example, you might want to identify the top 5 products sold, the top 10 revenue-generating clients, or the bottom three regions in terms of sales. With this skill, you’ll be able to easily identify the top or bottom values in your business data and make data-driven decisions. 

Let’s consider a scenario where you own a retail store and want to find out the top 5 products in terms of sales in the last month. We’ll use Power BI to analyze the data and present it visually appealingly.

Step 1: Import the data into Power BI

In Power BI Desktop, connect to your data source – this could be an Excel file, a CSV file, or any other supported format. For our example, let’s assume you have sales data in an Excel file. Here’s how you import it:

  1. Open Power BI Desktop.
  2. Click on “Home” in the top-left corner.
  3. Click on “Get Data” and select “Excel” from the dropdown.
  4. Browse your Excel file and click “Open.”
  5. Select the sheet containing your sales data and click “Load.”

Now your data is loaded into Power BI, and you can start building your visualizations.

Step 2: Create a simple bar chart

Let’s create a bar chart to visualize the sales for each product:

  1. Click on the “Bar chart” icon in the “Visualizations” pane on the right side of the screen.
  2. Click on the “Product” column in the “Fields” pane to add it to the “Axis” area.
  3. Click on the “Sales” column in the “Fields” pane to add it to the “Values” area.

Now you should have a basic bar chart showing the sales for each product.

Step 3: Apply the Top N filter

To display only the top 5 products in terms of sales, follow these steps:

  1. Click on the bar chart to select it.
  2. In the “Visualizations” pane, click on the “Filters” tab.
  3. Click on the “Product” dropdown under the “Visual level filters” section.
  4. Change “Basic filtering” to “Top N” by clicking on the dropdown.
  5. Set “N” to “5” and select “Sales” as the “By value” field.
  6. Click on “Apply filter.”

Now your bar chart should display only the top 5 products based on sales.

Step 4: Customize the visualization

Feel free to customize the appearance of the bar chart according to your preferences. You can change the colors, add data labels, or modify the axis titles. To do this, simply click the “Format” tab in the “Visualizations” pane and browse the various available options. 

Related Tags: