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,
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
Could you copy paste sample data or share your file?
@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.
Tagging some experts
Hi @Zubair_Muhammad ,
Agree with your approach however, without full data set is difficul to understand where you measure could have gone incorrect.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português
@MFelix wrote:Hi @Zubair_Muhammad ,
Agree with your approach however, without full data set is difficul to understand where you measure could have gone incorrect.
Regards,
MFelix
@MFelix @Zubair_Muhammad Please find my powerbi file with suggested Rank measure here: http://www.filedropper.com/samplefile_5
Thanks,
Raj
@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:
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 This is the result for me...it looks like it is ranking correctly according to the value but it is not taking into account Year or Grouping or Indicator. The highest rank should be around 200-300 as that is the max # of countries. Any idea what's going on?
@Zubair_Muhammad Can I send you the file in a private message? If not, I can post here but would rather avoid that.
Thanks,
-Raj
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |