Bottom N of Top N2

[Xynics]

Hi,

I have a list of responders to a survey which I have count(responder) shown as % of total by "location" name in a Matrix.

I want to display the top 100 locations in blocks of 25, ordered by the % responses in descending order.

What I want to do is select the Top 25 into one matrix, then the Bottom 25 of the Top 50 into the next matrix, etc.

I've been trying to use RANKX to order by % response DESC and select each group of 25 by >=1 and <=25, >=26 and <=50 etc.

I am sure someone else has likely come across this and there's a really simple solution, but I just can't see it!

Can anyone help?

Thanks

Mike

[LivioLanzo]

@Xynics

Are you able to post sample data?

[Xynics]

Hi, this is a sample of the data I am trying to display.

Location	% responders
The University Of Nottingham	6.23%
Nottingham Trent University	3.46%
Keele University	3.44%
University Of Birmingham	3.34%
Coventry University	3.28%
Cardiff University	3.08%
Kings's College London	2.87%
Liverpool John Moores University	2.51%
University College London (UCL)	2.47%
Manchester Metropolitan University	2.36%
University Of Leicester	2.31%
Newcastle University	2.26%
University Of Sheffield	2.10%
University Of Glasgow	2.00%
Southampton Solent University	1.90%
University Of Derby	1.82%
University Of Dundee	1.82%
Loughborough University	1.80%
De Monfort University Leicester	1.70%
Leeds Beckett University	1.64%
University Of Manchester	1.57%
University Of Salford	1.54%
Sheffield Hallamshire University	1.51%
University Of The West Of England	1.46%
Heriot-Watt University	1.41%

And assuming I was trying to do top 5 first, then 5-10 next, then 10-15 next, I am wanting to display it in PowerBI like this as seperate tables;

Location	% responders		Location	% responders		Location	% responders
The University Of Nottingham	6.23%		Cardiff University	3.08%		University Of Leicester	2.31%
Nottingham Trent University	3.46%		Kings's College London	2.87%		Newcastle University	2.26%
Keele University	3.44%		Liverpool John Moores University	2.51%		University Of Sheffield	2.10%
University Of Birmingham	3.34%		University College London (UCL)	2.47%		University Of Glasgow	2.00%
Coventry University	3.28%		Manchester Metropolitan University	2.36%		Southampton Solent University	1.90%

[LivioLanzo · Accepted solution]

Hi @Xynics

get the file: https://1drv.ms/u/s!AiiWkkwHZChHj0UEUdLX3DXEKpID

[Xynics]

Hi,

Thanks for the sample, however I think I may have mislead slightly.  My data is not already aggregated.

My data is in a format for example like this;

Responder ID	University
1	Nottingham
2	Sheffield
3	Leicester
4	Leicester
5	Leicester
6	Nottingham
7	Sheffield
8	Nottingham
9	Nottingham
10	Sheffield
11	Sheffield
12	Sheffield
13	Sheffield
14	Nottingham

I am then using a Matrix to count the Responder ID's for each University location, so I don't have a Measure that is the count of responders.

I guess I need a new measure that counts the responders to then apply the rank to that, but I also still need to be able to apply slicers etc.

Mike 

[LivioLanzo]

Hi @Xynics,

looks like you just need to change the measure to a count  of Respondend ID instead of the sum I used in my file

[Xynics]

Thanks for your help, I've managed it using your example and changing the sum to a count.

Couldn't see it yet so simple.

[AlexisOlson]

Yep. Just create a rank column and then use that column in your visual level filters to select what ranges you want to show using advanced filtering.