Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
NumeroENAP
Helper III
Helper III

TOPN by city

Hi, 

 

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

 

Here is an exemple of my model : 

EmployeCity%
JohnBuffalo40%
JuliaBuffalo38%
PedroSan Jose38%
CareySan Jose18%

 

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

 

Thanks

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

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

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.

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
Not applicable

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

Date sample

 

EmployeeCity%
JakeBoston0,93203883
MartinTampa Bay0,92977099
JamesNashville0,92857143
TuukkaCalgary0,92785105
BradenEdmonton0,92517986
ConnorMinnesota0,92410401
DevanToronto0,92271819
CoreyFlorida0,92125984
MikeMinnesota0,90125984

 

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

 

EmployeeCity%
ConnorMinnesota0,924
MikeMinnesota0,920

 

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.

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

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
Community Champion
Community Champion

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


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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.