Forum Discussion

aktripathi2506's avatar
aktripathi2506
Helper IV
10 years ago

Ranking function not working with slicer

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])))  ...
Need Help
MattAllington's avatar
MattAllington
Community Champion
10 years ago

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 :-)