Power BI Exercise – Create a Dynamic Top N Sales Report

Learn how to create a dynamic Top N sales report in Power BI. This exercise includes adding a slicer for selecting the Top N products and displaying their sales amounts in a table for easy analysis.

Tasks:

1. Load the dataset into Power BI.
2. Create a slicer for users to select Top N Products.
3. Create a measure for Top N.
4. Show the top N products in a table with their sales amount.

Solution:

Download Dataset: Sales Dataset

Load the dataset into Power BI:

Go to the Home tab > Click on Get Data > and select Text/CSV.

After selecting the file, you can see the data preview. Click on Load.

Create a slicer for users to select Top N Products:

To achieve this, we will create a numeric range parameter.

Go to the Modeling tab and select the Numeric Range from the New Parameter dropdown.

Name: Provide the name to Parameter: Select N

Minimum: Set to 1. Define the minimum value for the slicer parameter.

Increment: Set to 1. Specify the increment value, which will increase the range by 1.

Maximum: Set to 10. The slicer value can go up to a range of 10.

Default: Set to 1.

After that, click on the Create button.

As you can see in the screenshot below, a numeric range parameter has been created for the Select N slicer.

Create a measure for Top N:

Now, create two measures: Total Sales and Top N Sales.

Right-click on the Sales dataset and select “New Measure.”

Total Sales = SUM(Sales[Sales])

Top N Sales =
IF(
RANKX(ALL(Sales[Product]), [Total Sales]) <= 'Select N'[Select N Value],
[Total Sales],
BLANK()
)

Show the top N products in a table with their sales amount:

Add a table visual with the Product and Top N Sales measure. To check the result, adjust the slicer value to filter the Top N records in the table visual.

I hope you enjoyed this Power BI exercise.