Forum Discussion
Ranking Issues with Measures
I would like to create a Top 5 and Bottom 5 ranking based off of a Measure that is interactive based off of the filter selections.
A picture is posted below(Dummy Data).. That I will further explain
So in the table I have a Market Column which is in my "Geography" Table and I have a category column which is in my "Retailer Table"... POS USD TY and POS USD LY are both measures (Foreign Currency/USD Conversion Rate) ... IYA is another measure that is taking POS USD TY / POS USD LY *100... I would like to have another column that ranks The Top 5 IYA's and Bottom 5 IYA's...
As I choose my time filter -- Last Week ,Last 4 Weeks, Last 52 Weeks I want the table and ranking systems to change based off selections. Currenlty I'm stuck and don't know what formula to use.. TOPN as a visual filter does not work .. I know I need a Rankx formula.. Please help!!!
18 Replies
Perhaps something along the lines of:
MyRankTop = RANKX(ALLSELECTED('Table'),[IYA],[IYA],DESC) MyRankBottom = RANKX(ALLSELECTED('Table'),[IYA],[IYA],ASC) IsTop5 = IF([MyRankTop]<=5,1,0) IsBottom5 = IF([MyRankBottom]<=2,1,0)Greg_Deckler Thank you for the reply... Unfortunately this didn't work... It produced multiple lines with the same ranking... I'm looking to have the highest IYA's be ranked the top and the lowest IYA's ranked on the bottom... Any other ideas ?
You'll need to probably play with the "ties" optional parameter of RANKX. An alternative might be to fiddle around with TOPN.
https://msdn.microsoft.com/en-us/query-bi/dax/rankx-function-dax
https://msdn.microsoft.com/en-us/query-bi/dax/topn-function-dax
I would like to create a Top 5 and Bottom 5 ranking based off of a Measure that is interactive based off of the filter selections.
A picture is posted below(Dummy Data).. That I will further explain
So in the table I have a Market Column which is in my "Geography" Table and I have a category column which is in my "Retailer Table"... POS USD TY and POS USD LY are both measures (Foreign Currency/USD Conversion Rate) ... IYA is another measure that is taking POS USD TY / POS USD LY *100... I would like to have another column that ranks The Top 5 IYA's and Bottom 5 IYA's...
As I choose my time filter -- Last Week ,Last 4 Weeks, Last 52 Weeks I want the table and ranking systems to change based off selections. Currently I'm stuck and don't know what formula to use.. TOPN as a visual filter does not work .. I know I need a Rankx formula.. Please help!!!
Hi,
Share the link from where i can download your PBI file.
