Reply
Frequent Visitor
Posts: 10
Registered: ‎11-30-2018

Need Help with Percentile Ranking

[ Edited ]

Hi all,

 

I am having a hard time figuring out a good way to do this so I am looking for some help. Here is the scenario:

 

I have a data set with the following raw data: Country Name, Government Debt to GDP ratio, Gross National Income (GNI), Year. However keep in mind that some countries do not have data for certain years.

 

I use GNI in a measure [development category] to categorize countries as either Advanced, Developing, or Emerging based on their GNI value for a given year. (again some countries don't have data and thus don't have a category for certain years)

 

PowerBI chart1.png

Now, the ultimate goal is to calculate how Debt to GDP for a country compares to that of its peers in its development category during any given year, using percentile ranking. So for example, in 2011 where did Chile's Debt to GDP fall in comparision to other countries in its [development category] for that year? Ideally this would be another measure/column I could add to the table above.

 

My understanding is that to get a percentile ranking of something you need the (total # of ranks – rank)/total # of ranks.

 

So in my scenario I would need to first rank each country by its Debt to GDP ratio. I have created a RANKX measure that does this called [Gov Debt to GDP Rank]. This measure also makes sure not to include blanks in the ranking.

 

Government Debt to GDP Rank = IF(CALCULATE(SUM('TE Data'[Government Debt to GDP]),VALUES(Country[Select a Country]),VALUES('Date'[Date]))<>0, RANKX(FILTER(ALLSELECTED(Country[Select a Country]),CALCULATE(SUM('TE Data'[Government Debt to GDP]),VALUES('Date'[Date]))<>0),CALCULATE(SUM('TE Data'[Government Debt to GDP])),,ASC,Skip))

 

Next we would need to find the MAX of this measure (or some other proxy for total # of data points) which is where I start running into some problems. I am not sure how to approach this in a way that Power BI will calculate the max rank given all these variables (country/indicator/year). At first I thought maybe I could just use the total # of countries but that doesn't work since some years there are countries that don't have data and thus aren't included in the ranking. Using the MAX or MAXX functions on the [Gov Debt to GDP Rank] measure I have created doesn't seem to work as MAX is only compatible with a column or multiple values and not a measure...

 

Any help you guys could provide would be much appreciated. Let me know if you need any more info.

 

Thank you.

 

EDIT: Here is link to a sample PBIX file with the problem laid out, it is hosted on FileDropper.com: http://www.filedropper.com/simplifiedhelpfile

AlB Super Contributor
Super Contributor
Posts: 1,164
Registered: ‎11-12-2018

Re: Need Help with Percentile Ranking

Hi @rajivraina

 

Can you either share the pbix file or the table above in text, rather than image format, so that it can be copied easily?

Frequent Visitor
Posts: 10
Registered: ‎11-30-2018

Re: Need Help with Percentile Ranking

[ Edited ]

@AlB wrote:

Hi @rajivraina

 

Can you either share the pbix file or the table above in text, rather than image format, so that it can be copied easily?


As I cannot share the pbix file, I have a created a dummy table on excel with data going back a few years for you to play with. I'm just not sure how to uplaod it to here. How do you upload files?

 

I have the file hosted on OneDrive but I cannot make it accessible to anyone as company firewalls restrict that option. However, I can share it with people as long as I have their email address, so that is one option if you are comfortable with pming me your email?

Community Support Team
Posts: 1,317
Registered: ‎07-30-2018

Re: Need Help with Percentile Ranking

hi, @rajivraina

We couldn't share email address, Please share some sample data and expected output. You can upload it to OneDrive or others like: Dropbox and post the link here.

Do mask sensitive data before uploading.

 

 

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
AlB Super Contributor
Super Contributor
Posts: 1,164
Registered: ‎11-12-2018

Re: Need Help with Percentile Ranking

@rajivraina

 

You can also upload your anonymised sample file to a website like this or similar, where you don't need an account

Frequent Visitor
Posts: 10
Registered: ‎11-30-2018

Re: Need Help with Percentile Ranking

[ Edited ]

@AlB wrote:

@rajivraina

 

You can also upload your anonymised sample file to a website like this or similar, where you don't need an account


 

Here is a sample PBIX file: http://www.filedropper.com/simplifiedhelpfile

Frequent Visitor
Posts: 10
Registered: ‎11-30-2018

Re: Need Help with Percentile Ranking

Bumping for visibility