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

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

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

It works Many thanks for your help!

Do you recommend any books or training courses that differentiate Excel formulas with Power Pivot functions?

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

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. 🙂

Hello again,

And the online DAX reference pages are found here.

Br,
Anders