cancel
Showing results for
Did you mean:
Highlighted
Helper II

## Need Help With Calculating Percentile Ranks (using RANKX, MAX, etc.)

Hi all,

I am reposting this since I hadn't attached a dataset last time and once I did it was too late as nobody was veiwing it anymore. So here I am still running into the same issue... below is the scenario/my thought process:

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).

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 (shown in the sample file below).

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.

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 # of data points 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 doesn't seem to work as MAX is only compatible with a column or multiple values and not a measure...

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

Thank you.

2 REPLIES 2
Highlighted
Community Support

```Measure =
MAXX ( ALLSELECTED ( Countries[Country] ), [Government Debt to GDP Rank] )
```
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper II

@v-chuncz-msft wrote:

```Measure =
MAXX ( ALLSELECTED ( Countries[Country] ), [Government Debt to GDP Rank] )```

Hi Sam,

That measure works to get me the max value and I further use that to create the percentile ranks. However, I still need to be able to either filter the results by the development category or modify the measure you have made to find the max rank for each category sperately so that the resulting percentile ranks are according to category. Since development category is a measure, I cannot use it in a slicer. How can I do this?

Thanks,

Raj

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors