topic Re: TOPN by city in DAX Commands and Tips

TOPN by city

NumeroENAP — Thu, 06 Feb 2020 13:41:53 GMT

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

Re: TOPN by city

az38 — Thu, 06 Feb 2020 13:49:57 GMT

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

Re: TOPN by city

Anonymous — Thu, 06 Feb 2020 14:00:50 GMT

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.

Re: TOPN by city

NumeroENAP — Sat, 08 Feb 2020 02:05:40 GMT

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).

Re: TOPN by city

Anonymous — Sat, 08 Feb 2020 02:23:58 GMT

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

Re: TOPN by city

NumeroENAP — Sat, 08 Feb 2020 02:51:54 GMT

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

Re: TOPN by city

TheDataMustFlow — Sat, 08 Feb 2020 04:03:43 GMT

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.

Re: TOPN by city

NumeroENAP — Sat, 08 Feb 2020 04:38:16 GMT

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

Re: TOPN by city

NumeroENAP — Sat, 08 Feb 2020 04:41:30 GMT

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.