11 Replies Latest reply on Oct 26, 2018 12:48 PM by Ombir Rathee

# Creating a Top-N set from the Total of rows in a table

Hi there,

I'm trying to create a Top-20 set based on the highest measure values on the Table total column. I've tried to do this by going to the Dimension that represents the rows in the table 'Create/Set' and 'Top' by the appropriate fields but I can't see anything that would represent the row total in there - do I need to create a formula?

Unfortunately the workbook contains sensitive data so I can't upload it.

Thanks

Marie

• ###### 1. Re: Creating a Top-N set from the Total of rows in a table

Marie,

Can you explain with some dummy data? Not sure what is row total in your data.

Thanks,

AB

• ###### 2. Re: Creating a Top-N set from the Total of rows in a table

Hi Marie,

From your question I could understand like from total rows in a table you need to create a Top 20 records for a single dimension.

If my understanding is correct then you need to create Top 20 set for a dimension on "Number of records" measure.

• ###### 3. Re: Creating a Top-N set from the Total of rows in a table

Thanks for responding Ankit and Akram,  I've attached a screen grab of the issue - there's an image showing how the set has been selected alongside a table with the resulting output.   I want the table to give the top-30 rows (from a dimension) based on the total number of records - it seems to select the top few and then it's just random!

Thanks, Marie

• ###### 4. Re: Creating a Top-N set from the Total of rows in a table

Hi Marie,

I would suggest using rank for this: Rank(Sum(<Your Value>),'asc') or in your case: Rank(Sum(<Your Value>),'asc') < 21

Another alternative to allow your users to alter the Top-n (if you want to provide this level of control, would be to replace the "<21" above with a parameter such as:

Rank(Sum(<Your Value>),'asc') < [a parameter] +1

The "+1" allows you to display the intended value but with the ever so slightly more performant less-than "<" rather than less-than-or-equal-to "<="

Either way, once you have created your filter (it will be boolean), simpy drop this onto the filters, setting the value to "T"

Steve

• ###### 5. Re: Creating a Top-N set from the Total of rows in a table

Thanks for the suggestions Steve,

I'm not sure I quite understand... I've tried adding the first formula to the Set window and got an error message and then thought you may have been referring to creating a Calculation in the Measures but I've just got errors with both...

• ###### 6. Re: Creating a Top-N set from the Total of rows in a table

Bracket is at the wrong place in your calculated field 'Test'

RANK(SUM([Number of Records]),'asc')<21

• ###### 7. Re: Creating a Top-N set from the Total of rows in a table

Thanks Ombir

I've added it as a filter and selected 'T' but it's not changed anything.  I still have over 100 rows of data in the table.

• ###### 8. Re: Creating a Top-N set from the Total of rows in a table

See attached file. Is this you want ?

• ###### 9. Re: Creating a Top-N set from the Total of rows in a table

Hi Marie,

I think there was some confusion here and yes, my initial example calc was correct, the others had the bracket in the wrong place - corrected now.

See this screenshot of a dashboard:

Steve

• ###### 10. Re: Creating a Top-N set from the Total of rows in a table

Thanks Ombir, I've had a look at the book.  So would I need to create a Parameter and a Set?  I've had a go but it's still not working out...I have several years of data in separate columns, and then a total for all years (included from the Analysis/Totals menu, not the Measures)   - how do I set the set/parameter to tell it to use the Total column?

Thanks,

Marie

• ###### 11. Re: Creating a Top-N set from the Total of rows in a table

Like this ? If this is the case then you can't show the Grand total at the end using Analysis menu. Either you've to remove the grand total from view or display it like this.