cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Thejeswar Senior Member
Senior Member

Need help understanding why TOP N is not working the way it is expected to be?

Hi Guys,

I have a requirement to display TOP N and Bottom N values of a measure for a dimension.

 

But when I tried to implement TOP N, the results that I got seemed unfamiliar.

 

I tried using both the methods specified in the below two links. The Results are the same at the end

 

The Below are the links

http://www.pandatagroup.com/blog/post/power-bi-tip-dynamic-topn-reports-in-power-bi

 

https://blogs.msdn.microsoft.com/danrub/2016/03/19/dynamic-topn-ranking-in-power-bi/

 

I have attached the screenshot of my output with this post one after the other as mentioned in the two links aboveTOPN.PNG

 

TOPN1.PNG

 

Here is my question... 

 

As you can see from screenshots, inspite of the values of Sales Amount not being same for Denmark and SA, they are having the same rank. Similarly, why is Rank no. 5 not appearing. It can be understood that because Rank 3 is appearing twice, Rank 4 is not appearing. But what happened to Rank 5?

 

Please help me understand this kind of functioning of Rank and TopN functions....?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Need help understanding why TOP N is not working the way it is expected to be?

Hi @Thejeswar

Let me draw attention to your dataset, in the Country table, there are two rows for "Denmark" but with two different country ID, when I modify this both in Country table and Sales table by replacing country ID to the same one, i finnally get the correct rank order.

60.png

after deleting the same row for 'Denmark'

59.png

after modifing both the Country table and Sales table

 

Best Regards

Maggie

 

View solution in original post

4 REPLIES 4
Community Support Team
Community Support Team

Re: Need help understanding why TOP N is not working the way it is expected to be?

Hi @Thejeswar

I follow these two article and get this result, does it meet your requirement?

54.png

What I do is to create measures like:

SelectedTopNnumber =
MIN ( 'Top N option'[TopN] )
Rank =
RANKX ( ALL ( Table1 ), Table1[sales amount],, DESC )
Ar =
IF ( [Rank] <= [SelectedTopNnumber], "=>", "" )
Country Rank by salesamount =
CALCULATE (
    [sales amount],
    TOPN ( Table1[SelectedTopNnumber], Table1, Table1[sales amount] )
)

To figure out what cause the issue, it is appreciated for you to share some data model example and measureformula,especially .pbix file.

 

Best Regards

Maggie

Thejeswar Senior Member
Senior Member

Re: Need help understanding why TOP N is not working the way it is expected to be?




Hi @v-juanli-msft,

I am not able to share my pbix file in this post. 

 

So I am sharing the screenshots of my model

 

Datamodel.PNG

 

 

The Following are my DAX statements..

 

 

 

SelectedTopNNumber = IF(HASONEVALUE('TopN'[Top]),Min('TopN'[Top]),10)
Amount = SUM(Sales[Sales Amount])
Country rank by Amount = IF(HASONEVALUE(Country[Country]),RANKX(ALLSELECTED(Country),[Amount],,0))
Ar = IF([Rank] <= [SelectedTopNNumber],"=>","")
Rank = RANKX(ALL(Country),Sales[Amount],,DESC)

 The Result is as follows

testsnapshot.PNG

 

 

I also have attached screenshot of my tables with data 

 

Tables.png

 

 

 

But I still get the same result. Not sure what is going wrong with this?

 

Regards,

Thejeswar

Community Support Team
Community Support Team

Re: Need help understanding why TOP N is not working the way it is expected to be?

Hi @Thejeswar

Let me draw attention to your dataset, in the Country table, there are two rows for "Denmark" but with two different country ID, when I modify this both in Country table and Sales table by replacing country ID to the same one, i finnally get the correct rank order.

60.png

after deleting the same row for 'Denmark'

59.png

after modifing both the Country table and Sales table

 

Best Regards

Maggie

 

View solution in original post

Thejeswar Senior Member
Senior Member

Re: Need help understanding why TOP N is not working the way it is expected to be?

Thanks for your reply. I did not notice that.

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)