Finding the highest value for an expression

[Zyrotot]

So, I have two sets of data, in one I have the population of each state, and in the other one the information about each scholarship given (in the information includes the state of the school).

Is there a way for me to separate the number of scholarships given per state, and calculate the highest scholarship per capita?

Or I need to calculate each per capita manually?

Example tables:

Population table

State	Population
State1	5.568.245
State2	458.659
State3	1.569.123

Scholarship table

ID	Year	School	...	State
1	2010	XXX	...	State1
2	2010	XXX	...	State2
3	2010	XXX	...	State3
...	...	...	...	...
54896	2010	XXX	...	State2

[JordyWegman]

Hi Zyrotot,

Your tables should be linked based on the State. That makes it possible to measure this.

Sum(Population) / Count(Distinct ID)

Jordy

Climber

[Zyrotot]

They are linked, but this would only give me the country per capita, no?

I wanted the highest state per capita, like it automatically returns the per capita from state X, which is the highest one.

[Vegar]

Could this solve your problem?

If(Sum(Population)/Count(Distinct ID) =Max(TOTAL Aggr(Sum(Population)/Count(Distinct ID), State)), Sum(Population)/Count(Distinct ID))