Find largest dimension within a dimension

[#1]

Hi all,

This is my first post so I'll try to keep it as detailed as possible. What I'm trying to do is to find the largest dimension within a dimension.
Let me try to explain.

I have a list of locations with different categories of people.

Locations	Category	Number of people
A	X	532
A	Y	2346
A	Z	642
B	X	75345
B	Y	3452
B	Z	2457
C	X	624
C	Y	445
C	Z	5683

What I want to do is create a list which can produce the top category in each location. eg.

Location	Category
A	Y
B	X
C	Z

I have tried the following, but it always gives me the values for all locations A, B, and C combined:

=FirstSortedValue(Race,-Aggr(Count(DISTINCT SubID),Race))

Is there a way to do this?

Thanks all in advance!

LDK

[tresesco · Accepted solution]

Include the location dimension in Aggr and try like:

=FirstSortedValue(Race,-Aggr(Count(DISTINCT SubID),Location, Race))

[MK_QSL]

See Attached...

[er_mohit]

Try this

FirstSortedValue(Category,-Aggr([Number of people],Locations,Category))

[kumarnatarajan]

Hi,

Try below script

Ex:

Aggr(NODISTINCT FirstSortedValue(Category, -[Number of people]),Locations)

[#6]

Hi tresesco,

Thank you so much this helps!

LDK