Get a rank in my KPI

[EliasGPS]

Hello,

i'm already showing a bar chart with the top 10 countries with there highest forest area and a filter pane with all the countries. Now i want when a user select a country i want to get the ranking in the KPI.  In the screenshot you can see that when a select for example canada, i need to get ranking 3.

My code in the bar chart is as follows:

Dimension: =aggr(if(GeoType = 'Country', GeoAreaName), GeoAreaName)

Measure = Avg({1<[Series Name]={'AG_LND_FRSTN - Forest area (thousands of hectares)'}>} Value)

I have tried alot of measures in my KPI like using the rank() function but nothing works.

Thanks

[vikasmahajan]

Hi,

Could you try this :

Dimension: =If(GeoType = 'Country', GeoAreaName)

Measure =  Rank( Aggr( Avg({1<[Series Name]={'AG_LND_FRSTN - Forest area (thousands of hectares)'}>} Value),

, GeoAreaName)

Vikas

[justISO · Accepted solution]

Hi, have you tried something like this?:

aggr( rank( Avg({1<[Series Name]={'AG_LND_FRSTN - Forest area (thousands of hectares)'}>} Value)), GeoAreaName)

or

aggr( rank( avg({$<GeoAreaName=, [Series Name]={'AG_LND_FRSTN - Forest area (thousands of hectares)'}>} Value)), GeoAreaName)

[EliasGPS]

Thanks for your reply.

Your answer is good. But i need to get only the countries. Right now i'm getting all the GeoAreaName. I want only the GeoAreaName that is a country.

i tried something like this: aggr( rank( avg({$<GeoAreaName=, [Series Name]={'AG_LND_FRSTN - Forest area (thousands of hectares)'}>} Value)), if(GeoType = 'Country', GeoAreaName))

but it didn't work. Do you understand what i mean?

Thanks

[EliasGPS]

nevermind it worked thx fot your help!