Forum Discussion

rajivraina's avatar
rajivraina
Helper II
7 years ago

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 ...
Need Help
      • rajivraina's avatar
        rajivraina
        Helper II
        7 years ago
        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?

         

  • rajivraina's avatar
    rajivraina
    Helper II
    7 years ago
    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.