
Power Pivot

February 3, 2021

I have a problem with the RANKX function within Power Pivot. Can anyone help please?
I use this formula within Measures:-
=RANKX(ALL(dbase[Name]),[Sum of Sales])
It works fine within a Pivot table - it ranks Sales people based on their daily sales.
However, I am trying to embed it within the actual dataset with the "Manage" option and then Pivot on the new enhanced data.
It doesn't work! Does anyone know the correct formula to have it within the Manage area please?
Thanks
Gary


Trusted Members
Moderators

November 1, 2018


Power Pivot

February 3, 2021

Hi - please see attached file. This is purely how Excel would handle it. I have used Countifs to get a rank. I need to be to achieve the same thing in Power Pivot as there is a significant amount of data. You will see the ranking in the excel sheet is based on a unique ID. If the ranking was in the calculation area of PP it would give more flexibility on reporting.
Thanks
Gary


Trusted Members
Moderators

November 1, 2018

Actually, a calculated column gives you less flexibility since it will always be based on set criteria and not respond to any filters in place on your pivot, unlike a measure (though you can also create a measure to ignore filters).
Anyway, for your example, assuming the table is called Table1, you could use:
=RANKX(filter(Table1,Table1[ID]=earlier(Table1[ID])),Table1[Sales])

VIP

Trusted Members

December 7, 2016

Hello,
The PQ functions are very well documented in the Power Query M function reference site found online.
Use the information you find there to find what the M functions you need.
Br,
Anders


Trusted Members
Moderators

November 1, 2018

1 Guest(s)
