Sort Measures with RANK Function

 

 

This post will describe how to sort the measures by using the RANK() function. The sort will be descending with the largest values appearing on top. Here're the steps:

 

Step1:  Create a formula for each of the measures to be sorted so that if the measure is null, it'll be assigned a big negative number so that it'll appear at the bottom.

 

@Profit (rank)

//if value is null, make it negative so it will rank at bottom

if isnull(sum([Profit])) then -9999 else sum([Profit]) end

 

Step 2: Create a 'Sort By' parameter with a list of measures.

 

Step 3: Create a formula using the RANK() function to sort the measures. For example,

 

@Rank

CASE [Sort By]

When 'Quantity' then RANK([Quantity (rank)])

When 'Profit' then RANK([Profit (rank)])

When 'Sales' then RANK([Sales (rank)])

When 'Profit Ratio' then RANK([Profit Ratio (rank)])

END

 

Step 4: Place this @Rank formula in front of the dimension in the Rows shelf.

 

Step 5: Display the 'Sort By' parameter for users to sort.