TOPN by city

[NumeroENAP]

Hi, 

I'm having problem with the use of TOPN, for some reason. 

Here is an exemple of my model : 

Employe	City	%
John	Buffalo	40%
Julia	Buffalo	38%
Pedro	San Jose	38%
Carey	San Jose	18%

I want a measure that would give me only the top performer (%) by city.

Thanks

[Anonymous]

Go to Filter pane

click on city column and Select Filter type as top N

And and click on top and type 1 and then add measure(in your case % measure) by which you want top N.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[NumeroENAP]

For some reasons, it gives me the top 2 employees of 2 random cities (out of 22). However, I made a % measure (with DIVIDE) and inserted it in the TOPN (filter/TOP N | 2 / by value). 

[Anonymous]

Can you share the sample data and what output you are getting when you click on top N.

[NumeroENAP]

Date sample

Employee	City	%
Jake	Boston	0,93203883
Martin	Tampa Bay	0,92977099
James	Nashville	0,92857143
Tuukka	Calgary	0,92785105
Braden	Edmonton	0,92517986
Connor	Minnesota	0,92410401
Devan	Toronto	0,92271819
Corey	Florida	0,92125984
Mike	Minnesota	0,90125984

What I get from my report (FILTER : City, Filter type : TOP N, # : 2, by value : %)

Employee	City	%
Connor	Minnesota	0,924
Mike	Minnesota	0,920

[TheDataMustFlow]

When you put the data in a matrix with the city as columns, do you get one or two columns for Minnesota?

If you get two it's probably because there's a trailing space in a row somewhere in your data in the city column.

Try to clean up your data (trim the city column) and see if the problem persists.

[NumeroENAP]

I only have one column for minnesota and my datas are pretty clean 😞 

[NumeroENAP · Accepted solution]

I succeeded! I used that formula : 

TOP2RANK = CALCULATE(
       SUM('Table'[%]
                    );
             TOPN(2;'Table';'Table[created_index];DESC
       )
)

Of course, I sorted my table with Power Query first.

[az38]

Hi @NumeroENAP 

try new calculated table

Table 2 = 
ADDCOLUMNS(
SUMMARIZE('Table';'Table'[City];"%";MAX('Table'[%]));
"Employee";
LOOKUPVALUE('Table'[Employe];'Table'[City];[City];'Table'[%];[%])
)

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn