We want to rank the products by their Total Sales . The Ranking number will be as per user selection.

Our Data Model contains three tables Orders, Products and Order_Details.

The dimension tables are Orders, Products. The fact table is the Order_Details table.

• The Orders table has a details of all the orders in our dataset
• The Products table has a details of all the products in our dataset
• The Order_Details has a line item details of the orders.

The Orders, Products table are connected to our Order_Details table in a one to many relationship.

RANK Products by Sales.

We want to rank the products by Sales. Create a new measure and Name it as Total Sales.

To Rank the Values, we need to specify the number we want. That is the Rank the Top 3 values  or Rank the Top 5 Values. So we create a Numeric Parameter. This numeric parameter will help us determine this value.

Go to Modelling – Numeric Range

Create a parameter. Name it RankValue. The Minimum Value is 1 and Maximum Value is 10.

Once we create a NumericRange. We get a Slider on the Report.

To create the Rank. We create a new measure . Name it Rank Top.

Next add the matrix visual. In rows add productname (from Products Table) and in values add the RankTop Measure. This will give us the ProductName and the Sales of the Product as per the Numeric Range given in the Slider. That is ranked values.

So if we select 6 in the Numeric Slider. It will give the products with the Top 6 sales.

If you move the slider across, the number changes. And you will get the Top values as per the slider numeric parameter.

Now if you look at the grand total it is 1354459. However it is not the value you will get if you sum to above values.

This value of 1354459 is the overall Total Sales in our dataset. As the Grand total is not having any filter associated with it , we get this value.

What we want is the grand total to show the sum of the above values.

To achieve this, we create a new measure. Name it as Rank Sum.

Now add this measure to our matrix visual. If you look at the Grand Total , it will give us the sum of the above values.

The Rank Top and the Rank Sum measure gives the same value for all the rows. Except on the Grand Total.

The Rank Top gives the overall sales as the Grand Total.

Whereas the Rank Sum gives us the sum of the top values as the Grand Total.

To view the video tutorial. Refer below.