Forum Discussion
Ranking data across multiple categories within same column
Hi,
I have a table of data that has macroeconomic data on countries over a period of years. I am trying to assign a rank to each country based on their value, while still taking into account the Year, Indicator, and Grouping columns AND skipping over any blank Values. The end result would read like the "Rank" column in the below sample picture. I've tried adapting lots of different forum posts to my problem but nothing has been successful and I just cannot seem to figure it out..I know it should be possible with some combination of the Rankx(Filter(All( functions.
To me the logic would be something like: If the Value is not blank then Rank the country's value against all other nonblank country values within the same Indicator, Grouping, and Year.
I would also probably need to use RANKX function as I need a way to switch from ASC to DESC ranking depending on the Indicator, as for some indicators the lower value the better while others a higher value the better.
Finally, all the data is from one table and the columns are either calculated columns or just raw data columns, no measures. The "Rank" column will likely need to be a measure.
I really appreciate any help, thanks in advance.
-Raj
11 Replies
Zubair_Muhammad wrote:Could you copy paste sample data or share your file?
Zubair_Muhammad I don't seem to have an option to attach files directly on this forum, so I have uploaded it to file sharing service "filedropper", here is the link: http://www.filedropper.com/data1
Let me know if you need anything else.
Try this MEASURE.
See the attached file as well with your sample data
Measure = RANKX ( CALCULATETABLE ( Table1, ALLEXCEPT ( Table1, Table1[ Grouping ], Table1[ Indicator ], Table1[ Year ] ) ), CALCULATE ( SUM ( Table1[ Value ] ) ), , DESC, DENSE )
Zubair_Muhammad wrote:Could you copy paste sample data or share your file?
Indicator Grouping Country Year Value GDP High Income United States 2007 14477640000000.00 GDP High Income United States 2006 13855890000000.00 GDP High Income Canada 2007 1464980000000.00 GDP High Income Canada 2006 1315420000000.00 GDP Middle Income Mexico 2007 1052700000000.00 GDP Middle Income Mexico 2006 975390000000.00 GDP Middle Income Chile 2007 173600000000.00 GDP Middle Income Chile 2006 154800000000.00 Balance of Trade High Income Canada 2007 42449060026.27 Balance of Trade High Income Canada 2006 41839680995.80 Balance of Trade Middle Income Chile 2007 24083120000.00 Balance of Trade Middle Income Chile 2006 22897170000.00 Balance of Trade Middle Income Mexico 2006 -6133210000.00 Balance of Trade Middle Income Mexico 2007 -10073730000.00 Balance of Trade High Income United States 2007 -705376000000.00 Balance of Trade High Income United States 2006 -761715000000.00 Zubair_Muhammad Above is the sample data in case you do not want to click link (understandable haha)
Thanks again.
