How to find the city with the largest number of students studying outside their place of birth?

[citizenbh]

How to find the city with the largest number of students studying outside their place of birth?

Sample:
Name Born Study
Name1 London London
Name2 London Paris
Name3 Paris Paris
Name4 London New York
Name5 London Paris
Name6 Paris New York
Name7 Paris Paris
Name8 New York Paris
Name9 New York New York

Result: London, 3

DAX ?
Excel Function ?
SQL?

 

[ca.moazzam]

Assuming that the above data is in A to C columns, you enter the following formula in D column =COUNTIF(B$2:B$10,B2) and copy it down. It will mention the number of times the city name repeated in column B which is what you are looking for. An alternate could be Pivot Table on this data.

 

[citizenbh]

It does not give the right result. Based on the table results should be London, 3

 

[ca.moazzam]

I see. Assuming your data is in cells A1 to C10 (Name, Born, Study), formula to find total number of students studying abroad: {=SUM(IF(B2:B10<>C2:C10,TRUE,FALSE)*1)}. Formula for finding such students from individual cities: {=SUM(IF(($B$2:$B$10<>$C$2:$C$10)*($B$2:$B$10=A13),TRUE,FALSE)*1)}. Please note that in the individual cities formula, you have to keep that city name in A13 or alternatively write "London" or that city name with quotes instead of A13. Do also note that the curly brackets above don't need to be entered using the keyboard. You write the formula and then press Ctrl+Shift+Enter. This will show curly brackets before and after the formula.

 

[citizenbh]

Thx,
I found a similar solution but the long way
In the table I added field (Column D)
Sign: = IF ([@ Born] <> [@ Study], 1.0)
and made a Pivot Table
then reference in F5, G6 on the first row in the pivot table
Image Table with Data:

Image Table with Formulas:

Even better solution in the formula:
= GETPIVOTDATA ("Sign", $F$4, "Born", "London")

replace "London" with F5

Now:
= GETPIVOTDATA ("Sign", $F$4, "Born", F5)

OR

in Access SQL Query for List the Cities:

SELECT tStudent.Born, Count(tStudent.Study) AS CountOfStudy
FROM tStudent
WHERE (((IIf([tStudent]![Born]=[tStudent]![Study],0,1))<>False))
GROUP BY tStudent.Born
ORDER BY Count(tStudent.Study) DESC;

 

[citizenbh]

Still remains a problem in the DAX