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.
