Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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
Solved! Go to Solution.
I succeeded! I used that formula :
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).
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 |
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 :
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
21 | |
20 | |
19 | |
13 | |
12 |
User | Count |
---|---|
42 | |
28 | |
23 | |
22 | |
22 |