Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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....?
Solved! Go to 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
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
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
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
Thanks for your reply. I did not notice that.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |