Need Help With Ranking and Counting Nonblank Rows (according to criteria)
I am having a hard time with figuring out what I believe should be two relatively simple measures that I will eventually use to calculate the percentile rank of a data point within a set of data.
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 ameasure [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).
My understanding is that to get a percentile ranking of something you need the (total # of ranks – rank)/total # of ranks, or (X-Y)/X. So I need to calculate X and Y for my dataset.
My end goal is to find the percentile rank of a country WITHIN its development category. For example, Norway's Debt to GDP ratio might be ranked #3rd in its "Advanced" group for year 2011 and the total number of countries with data for that category and year are 23, so (23-5)/23 would place Norway in the 78th percentile of the "Advanced" group.
First off, thank you for taking the time to help me out.
Your solution seems to be on the right track, but measure "total" is not getting the correct values. I'm assuming this measure's goal is to count the total number of nonblank rows with data in it for that category/year. The current measure looks to output 43, but the max number of countries (according to the "Development Categories" measure) in the advanced bucket is only 26 in any given year, for Developing it is around 75 and Emerging has around 100+ countries in it. So there shouldn't be 43 I don't think?
Also, the measure "condition" and resulting measure "total" seem to not work for countries in any category other than Advanced. Not sure what's going on there.
If you could take another look that would be greatly appreciated.