Slicer Problem with Rank Function

[zarealshook]

Hi Everyone,

I am working on a practice file with help of a video explaining every step to create a dashboard file. Everything went fine and clearly understood until I came to the Rank function to get the top rank of the products. The function worked fine but when I click on any slicer to test it, the "Rank" function returns "#VALUE" as you can see from the attached file.

I am unable to figure out what might have gone wrong here. Any help is really appreciated.

Attached you can find the file I am working on for your easy reference.

Regards,

Zare

 

[johnny51981]

Hello.
Can you show Column Y in this? Your ranking formula is pointing to that column, but can't see what is happening there in the provided screenshots.

 

[zarealshook]

Hello.
Can you show Column Y in this? Your ranking formula is pointing to that column, but can't see what is happening there in the provided screenshots.

Hi Johnny,

Thanks for passing by. I thought no one would respond to my inquiry, though!

I am attaching herewith a screenshot of Column "Y" for your easy reference.

Hope I can get this fixed the soonest possible.

Regards,

Zare

 

[johnny51981]

Rather than replicating your Pivot Table with Offset, are you able to create a ranking using the Pivot as your source? I tend to avoid Offset (just because it hasn't clicked in my head yet), but my guess would be that it doesn't like to play so nicely with how Pivot Table move.

 

[zarealshook]

Rather than replicating your Pivot Table with Offset, are you able to create a ranking using the Pivot as your source? I tend to avoid Offset (just because it hasn't clicked in my head yet), but my guess would be that it doesn't like to play so nicely with how Pivot Table move.

Yes, Johnny, I am trying to use the ranking using the Pivot as my source. Any ideas on how to fix it with the offset? I just couldn't understand how she (in below link video) turned the rank function to read (=RANK(Y3#,Y3#)!!! Would you please be so kind to go over the below link video and advise how did she do it?

Thanks and best regards,

Zare