Need Help With Ranking and Counting Nonblank Rows (according to criteria)

[rajivraina]

Hi all,

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

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. 

I have attached my PBIX file which lays out the problem really well if you don't understand my explanation above, and you can play around with it yourself: http://www.filedropper.com/simplifiedhelpfile

Please let me know of any ideas or solutions you may have.

Thank you,

Raj

[v-juanli-msft · Accepted solution]

Hi @rajivraina

Sorry for replying so late.

I work with a workaround, it changea your relationships among tables, please download my pbix and see the details.

1.in "GNI" table,

create calcuated columns:

year = YEAR(GNI[Date])

GNI Rank_column =
RANKX (
    FILTER ( ALL ( GNI ), [year] = EARLIER ( GNI[year] ) ),
    [GNI per capita, Atlas method (current US$)],
    ,
    DESC,
    DENSE
)


Category_column =
SWITCH (
    TRUE (),
    'GNI'[GNI Rank_column] < 26.5, "Advanced",
    AND ( 'GNI'[GNI Rank_column] >= 26.5, 'GNI'[GNI Rank_column] < 101 ), "Developing",
    'GNI'[GNI Rank_column] >= 101, "Emerging",
    BLANK ()
)

merged_G= GNI[Country]&GNI[year]

2.In "Debt to GDP" table, create calcuated columns

merged_d = 'Debt to GDP'[Country]&'Debt to GDP'[Year]

then create relationship between "GNI" table and "Debt to GDP" table based on [merged_G] and [merged_d]

3.In "Debt to GDP" table, create measures:

rank_value = SUM('Debt to GDP'[Government Debt to GDP])

rank_new =
IF (
    [rank_value] = BLANK (),
    BLANK (),
    RANKX (
        FILTER (
            ALLSELECTED ( GNI ),
            [rank_value] <> BLANK ()
                && [Category_column] = MAX ( GNI[Category_column] )
        ),
        [rank_value],
        ,
        ASC
    )
)


max_percate =
MAXX (
    FILTER (
        ALLSELECTED ( GNI ),
        [rank_value] <> BLANK ()
            && [Category_column] = MAX ( GNI[Category_column] )
    ),
    [rank_new]
)


final_output = ([max_percate]-[rank_new])/[max_percate]

“Community Support Team _ Maggie Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

“

[rajivraina]

Thank you so much for your help, this works wonderfully.

[v-juanli-msft]

Hi @rajivraina

Please refer to my pbix where i create new measures in "Debt to GDP" table.

Best Regards

Maggie

[rajivraina]

Hi @v-juanli-msft,

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.

Thanks,

Raj