cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Ranking data across multiple categories within same column

Hi,

 

I have a table of data that has macroeconomic data on countries over a period of years. I am trying to assign a rank to each country based on their value, while still taking into account the Year, Indicator, and Grouping columns AND skipping over any blank Values. The end result would read like the "Rank" column in the below sample picture. I've tried adapting lots of different forum posts to my problem but nothing has been successful and I just cannot seem to figure it out..I know it should be possible with some combination of the Rankx(Filter(All( functions.

Capture.PNG

 

To me the logic would be something like: If the Value is not blank then Rank the country's value against all other nonblank country values within the same Indicator, Grouping, and Year. 

 

I would also probably need to use RANKX function as I need a way to switch from ASC to DESC ranking depending on the Indicator, as for some indicators the lower value the better while others a higher value the better. 

 

Finally, all the data is from one table and the columns are either calculated columns or just raw data columns, no measures. The "Rank" column will likely need to be a measure.

 

I really appreciate any help, thanks in advance.

 

-Raj

11 REPLIES 11
Super User III
Super User III

Re: Ranking data across multiple categories within same column

@rajivraina 

 

Could you copy paste sample data or share your file?

Try my new Power BI game Cross the River
Highlighted
Helper II
Helper II

Re: Ranking data across multiple categories within same column


@Zubair_Muhammad wrote:

@rajivraina 

 

Could you copy paste sample data or share your file?


@Zubair_Muhammad  I don't seem to have an option to attach files directly on this forum, so I have uploaded it to file sharing service "filedropper", here is the link: http://www.filedropper.com/data1

 

Let me know if you need anything else.

Highlighted
Helper II
Helper II

Re: Ranking data across multiple categories within same column


@Zubair_Muhammad wrote:

@rajivraina 

 

Could you copy paste sample data or share your file?


IndicatorGroupingCountryYearValue
GDPHigh IncomeUnited States200714477640000000.00
GDPHigh IncomeUnited States200613855890000000.00
GDPHigh IncomeCanada20071464980000000.00
GDPHigh IncomeCanada20061315420000000.00
GDPMiddle IncomeMexico20071052700000000.00
GDPMiddle IncomeMexico2006975390000000.00
GDPMiddle IncomeChile2007173600000000.00
GDPMiddle IncomeChile2006154800000000.00
Balance of TradeHigh IncomeCanada200742449060026.27
Balance of TradeHigh IncomeCanada200641839680995.80
Balance of TradeMiddle IncomeChile200724083120000.00
Balance of TradeMiddle IncomeChile200622897170000.00
Balance of TradeMiddle IncomeMexico2006-6133210000.00
Balance of TradeMiddle IncomeMexico2007-10073730000.00
Balance of TradeHigh IncomeUnited States2007-705376000000.00
Balance of TradeHigh IncomeUnited States2006-761715000000.00

 

@Zubair_Muhammad Above is the sample data in case you do not want to click link (understandable haha)

 

Thanks again.

Highlighted
Super User III
Super User III

Re: Ranking data across multiple categories within same column

@rajivraina 

 

Try this MEASURE.

See the attached file as well with your sample data

 

Measure =
RANKX (
    CALCULATETABLE (
        Table1,
        ALLEXCEPT ( Table1, Table1[ Grouping ], Table1[ Indicator ], Table1[ Year ] )
    ),
    CALCULATE ( SUM ( Table1[ Value ] ) ),
    ,
    DESC,
    DENSE
)
Try my new Power BI game Cross the River
Highlighted
Helper II
Helper II

Re: Ranking data across multiple categories within same column


@Zubair_Muhammad wrote:

@rajivraina 

 

Try this MEASURE.

See the attached file as well with your sample data

 

Measure =
RANKX (
    CALCULATETABLE (
        Table1,
        ALLEXCEPT ( Table1, Table1[ Grouping ], Table1[ Indicator ], Table1[ Year ] )
    ),
    CALCULATE ( SUM ( Table1[ Value ] ) ),
    ,
    DESC,
    DENSE
)

@Zubair_Muhammad This is the result for me...it looks like it is ranking correctly according to the value but it is not taking into account Year or Grouping or Indicator. The highest rank should be around 200-300 as that is the max # of countries. Any idea what's going on?Capture2.PNG

 

Highlighted
Super User III
Super User III

Re: Ranking data across multiple categories within same column

Could you share file or full data
Try my new Power BI game Cross the River
Highlighted
Helper II
Helper II

Re: Ranking data across multiple categories within same column

@Zubair_Muhammad Can I send you the file in a private message? If not, I can post here but would rather avoid that.

 

Thanks,

 

-Raj

Highlighted
Super User III
Super User III

Re: Ranking data across multiple categories within same column

@rajivraina 

 

Tagging some experts

 

@MFelix 
@TomMartens 

 

 

Try my new Power BI game Cross the River
Highlighted
Helper II
Helper II

Re: Ranking data across multiple categories within same column


@Zubair_Muhammad wrote:

@rajivraina 

 

Tagging some experts

 

@MFelix 
@TomMartens 

 

 


Any luck guys?

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors