Log In
Register
Home
February 3, 2021
Offline
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
Offline
I presume you mean you are trying to use it in a calculated column? If so, I think we'd need to see the model and know what level you want the ranking done at - e.g. should it always be daily sales, regardless of what aggregation you are looking at in the pivot table(s)?
February 3, 2021
Offline
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
Offline
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])
February 3, 2021
Offline
It works Many thanks for your help!
February 3, 2021
Offline
Do you recommend any books or training courses that differentiate Excel formulas with Power Pivot functions?
VIP
Trusted Members
December 7, 2016
Offline
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
Offline
Do you recommend any books or training courses that differentiate Excel formulas with Power Pivot functions?
Well, given where we are, I think I'd have to suggest you look at Mynda's Power Pivot courses. 🙂
1 Guest(s)
