Dynamically Rank rows in PivotTable based on a measure and the current context.

Question

Hi Guys,

I'm basically trying to write a DAX measure that replicates the good old Excel 'Rank' function whereby you rank a given number by the column that its in.

I have a measure [Loyalty Conversion] which represents the number of potential customers converted to out loyalty programme and I have a bunch of different reports that measure this across a number of dimensions such as stores, regions, dates etc. My inital attempt has invlved running the measure through the RANKX function:

=RANKX(ALL('Store Attributes'[Store]),[Loyalty Conversion]) 

The issue I have is that as things stand not only do I have to hard code the column into the measure but the thing fundamentally doesn't work if I want to only show a subset of the dimension in question (because the 'All' opens up the whole thing). I need to ensure that the measure takes into account the current context of the PivotTable.

Any ideas?

Jacob

Answer 1

Check this thread, it may help you: Displaying Rank Dynamically irrespective of dimension selection using MDX Calculated measure

Answer 2

Thanks Irusul - will check it out.

Jacob