Ranking function not working with slicer

[aktripathi2506]

I have these three columns in my table.

Now I want to show top 5 based on the maximum hours.

I have applied following formula

RANKX(ALLSELECTED('Table'[Name]),CALCULATE(SUM('Table'[Hours])))

But I have a year slicer too, when I apply the slicer this rank go off…

Meaning this is ranking all despite of slicer, so when I select for example 2015 then rank could be 1,2,3,6 (4,5 missing i.e. rank number in between are missing)

Am I doing any logical mistake here, please advice

This is not the real data, this is just for understanding.

Name	Hours	year
A	13	2015
B	45	2015
C	23	2015
D	12	2015
E	34	2015
F	23	2015
G	65	2015
H	34	2015
I	32	2015
A	23	2016
B	24	2016
C	54	2016
D	34	2016
E	64	2016
F	31	2016
G	12	2016
H	18	2016
I	38	2016

[MattAllington]

I suggest you extend your data model into a star schema.  Read my article here http://exceleratorbi.com.au/the-optimal-shape-for-power-pivot-data/

Then create a Year table and a Name table. and put the columns from those table in your visual. 

then write this formula

RANKX(ALL(NameTable),CALCULATE(SUM('Table'[Hours])))

Alternatively you could just use this,

RANKX(ALLEXCEPT('Table','Table'[Year]),CALCULATE(SUM('Table'[Hours])))

but that would miss a learning opportunity 🙂