Question regarding Top X/TopN

[rhinofromTurkeyParticipant Started: 0Replies: 7Total: 7]

I have a list of users who have logged in to my application and I’m using SQL Server.  I have  a separate table that collects the daily visitor’s GUIDs

Anyway the data is linked/merged/etc and appears as follows

UserA – May 14
UserB – May 14
UserC – May 14
UserA – May 13
UserD – May 13
UserA – May 12
UserD – May 12
UserE – May 12

The count of the users is:

UserA – 3
UserB -1
UserC -1
UserD – 2
UserE -1

Now – I want to Rank and get a Top N – however the issue – I believe I have is that I am using Excel 2010 (organization is upgrading soon)

I’ve read several posts on powerpivotpro – but I cannot find a good way to get the Count to translate into a RANK and then have a slicer be able to be used for Top5/10/20/etc.

Is there a good way to approach this – I can use RANK as an excel function but not as a measure.  Do I have to do the old V1 way (stated in an old Rob Collie post)  OR is there an easier way??

I could do the old select the filter -> top N. But I want to make it more dynamic

Thoughts? Help

Thank you

Rhinmo

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Hello Rhino,

Just want to make sure I would be working with the right table. I think it is this one:

UserA – 3
UserB -1
UserC -1
UserD – 2
UserE -1

and not the first table?

and not this table:

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Hi Rhino,

It can be done in Excel 2010. See attached worksheet.

In addition to what you can see in the formulas, I also used the Sort By for User on column that stored rank.

I learned how to do this from Rob Collie’s Comprehensive Power Pivot Course.

Attachments:

You must be logged in to view attached files.

[rhinofromTurkeyParticipant Started: 0Replies: 7Total: 7]

I have to upgrade my powerpivot version… :-/  – might be why I dont have the full functionality/feature-set

I’ll report back  (see how IT long IT admins take)…

Thank you for your time and effort

 

I took the 2 day course and I signed up for the 1 year membership to PPU… I’m learning something else now and will be getting to the PPU shortly.

I’m not sure which one you’re referring to?

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Rhino,

It may be different now, but when I took a Rob Collie’s course at the Power Pivot University (PPU), the course was called “Comprehensive Power Pivot”.

[rhinofromTurkeyParticipant Started: 0Replies: 7Total: 7]

I used the sheet at home and the issue I have at work is the RANKX function is not available on v1 – which would make this a non-issue.

So my issue really is a non-issue… if I had the correct version of PowerPivot – thanks for your help

 

The “real world” examples I have to deal with make learning PowerPivot more exciting, since I get to see how far I can take the product and how to introduce new things I read about on the blog.

Thanks again

Rhino

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Hello Rhino,

Remember to you can upgrade your work copy of Power Pivot also to the newest version.

[Author]

Posts