Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
arulsettu
Frequent Visitor

Rank for particular years

hi i need to show the rank of the countries for praticular years

 

my expreession for rank is

 

Rank = RANKX(ALL('table'[Country]),[Sum_value],,0)

 

i am getting rank for the countries

 

my next step is i want rank for previous year

 

CALCULATE([Rank],FILTER('table','table'[Year]=2012)) 

 

but i am getting 1 for all the countries

 

can any one help on this

 

10 REPLIES 10
Anonymous
Not applicable

Hi

 

  Can you share some sample data and how you want to get the result?

 

Just to say in general, you can get rank for country and year as a separate column.

Rank.png

 

 

i created like the image you posted 

 

Rank_dimension = RANKX(ALL('table'[Country]),'table'[Country])

 

iam getting the different ranks. upto this fine

 

now i need the rank for 2 years

 

example 2016 and 2012

 

2012_rank = CALCULATE(SUM([Rank_dimension]),FILTER('table','table'[Year]=2012))

 

2016_rank = CALCULATE(SUM([Rank_dimension]),FILTER('table','table'[Year]=2016))

 

 i am getting same rank for both years

 

can you suggest me how to approach this issue

 

thanks

Anonymous
Not applicable

Hi

 

  It might the same number of rows for both the Year. 

 

 In the below screenshot, if you take the Rank_dimension, it has the same number of values because it has same 4 entries. In case if you have a different number of rows then you will get different values.

 

Rank1.png

 To show that, I created another 2 column 2012Yr & 2013Yr with RankYear column and it shows 4 & 8 values.

 

 

This is the output i am getting

 

Capture.JPG

 

can you see china has different values for 2016 and 2017 but getting same rank

 

can you help me on this

 

 

Hi @arulsettu,

 

Please sort your data by year, the different value with same rank mean they have same rank in different category.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Thanks for the reply

 

but i have different values for 2012 and 2016

 

even getting same rank

 

can you please explain how its defininig the rank

 

Rankx(All(table[country],table[copuntry]))

 

here we are not passing any values right. how its bringing the rank

 

and why i am getting same rank

 

Thanks

This is my script for column

 

Rank_dimension = RANKX(ALL('table'[Country]),[Sum_value],,0)

 

for this i am getting below output

 

rank outputrank output

 

my year like this

2011

2012

2013

2014

 

its in whole number data type

 

i created everything as measure. do you wanr me to create as column

Anonymous
Not applicable

Hi

 

Are you ok with the given format? If that is fine then you can replicate the same and get the result.

 

Thanks

Hari

arulsettu
Frequent Visitor

any idea guys ?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.