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

[Thejeswar]

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 above

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

[v-juanli-msft]

Hi @Thejeswar

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

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]

---

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

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

I also have attached screenshot of my tables with data 

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

Regards,

Thejeswar

[v-juanli-msft · Accepted solution]

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.

after deleting the same row for 'Denmark'

after modifing both the Country table and Sales table

Best Regards

Maggie

[Thejeswar]

Thanks for your reply. I did not notice that.