3 Replies Latest reply on Aug 7, 2012 10:42 AM by Tracy Rodgers

# Filter (Top 10 Highest or Lowest) and Sorting on Multiple Measures

I have one Brand Table where more than 50 brands are presented. I have to filter the data on multiple measures (Calculated field). It means I have to give flexibility to user that if he select any measure from filter or any other way, the Brand table should be filtered on that particular measure and should show Top 10 Highest or Lowest valued Brand. For example: There are three measure A, B, C. So when user will select A from drop down the table should be filtered on A, when User select B then the Table should be sorted on B and so on.

Please show me the way, How to approach this problem????

• ###### 1. Re: Filter (Top 10 Highest or Lowest) and Sorting on Multiple Measures

Hi Ajit,

This can be done by creating a string parameter that lists out all the measures. Then, use this parameter in a calculation similar to the following:

case [Choose Your Measure Parameter]

when 'Sales' then sum(Sales)

when 'Profit' then sum(Profit)

when 'Discount' then avg(Discount)

end

Then, place the dimension on the filter shelf and select the Top tab. Select By Field: and choose the above calculation from the drop down menu list. Click OK. Then, right click on the parameter and select Show Parameter Control.

Hope this helps!

-Tracy

1 of 1 people found this helpful
• ###### 2. Re: Filter (Top 10 Highest or Lowest) and Sorting on Multiple Measures

Hi Tracy

This is really helpful but still there is a question.

So now I am able to filter the table on the basis of Measures. But is it possible to create something like parameter or filter for Top 10 highest and Top 10 lowest.

For example – Suppose User filtered the table on one of the Measure by selecting from the Parameter. Now he want to see the Top 10 highest or Top 10 lowest. SO is it possible to give any Parameter or filter, from there user can filter the Top 10 highest and Top 10 lowest

Thanks

Ajit

• ###### 3. Re: Filter (Top 10 Highest or Lowest) and Sorting on Multiple Measures

Hi Ajit,

First, place the calculation from the above post on the view and sort the dimension in the desired order. Then, to get the top/bottom another parameter and calculated field should be created. Create a string parameter (i.e. Top or Bottom 10) and create a list with Top, Bottom.

Then, create a calculated field using this parameter similar to the following:

case [Top or Bottom 10]

when 'Top' then index()<=10

when 'Bottom' then last()<=9

end

Place the above calculation on the filter shelf and check True. (Make sure that the calculation is being computed in the correct way, i.e. Table down). Then, show the parameter control.

Hope this helps!

-Tracy

1 of 1 people found this helpful