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

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.

“