CUBERANKEDMEMBER function
Returns the nth member of a set — the way to build “Top 5” lists from a Data Model.
Syntax
=CUBERANKEDMEMBER(connection, set_expression, rank, [caption])How to use CUBERANKEDMEMBER in Excel, step by step
CUBERANKEDMEMBER doesn’t run in a web practice sheet because it reads a data model (Power Pivot / OLAP) connection. Follow these steps in Excel itself.
You’ll need
- A Data Model or OLAP connection
- A CUBESET (sorted the way you want)
- Put your data in a table. Click inside your data, press Ctrl+T, tick “My table has headers” and click OK. Give it a clear name on the Table Design tab (for example Sales).
- Add the table to the Data Model. Go to Insert › PivotTable › From Table/Range, tick “Add this data to the Data Model” and click OK. Excel now has a connection called ThisWorkbookDataModel.
- Build a quick PivotTable first. Drag a field (for example Region) to Rows and a number (for example Amount) to Values. This creates the measure the CUBE functions will read, such as [Measures].[Sum of Amount].
- Let Excel write the formulas for you. Click the PivotTable, then PivotTable Analyze › OLAP Tools › Convert to Formulas. Every cell becomes a CUBEMEMBER or CUBEVALUE formula you can copy and edit.
- Make a sorted set. In B1: =CUBESET("ThisWorkbookDataModel","[Sales].[Product].Children","Products",2,"[Measures].[Sum of Amount]").
- Pull the top members. In A3: =CUBERANKEDMEMBER("ThisWorkbookDataModel",$B$1,ROWS($A$3:A3)) and copy down five rows.
- Add their values. In B3: =CUBEVALUE("ThisWorkbookDataModel",A3,"[Measures].[Sum of Amount]") and copy down.
Example
=CUBERANKEDMEMBER("ThisWorkbookDataModel",$B$1,1)Result: The best-selling product
Use ROWS($A$3:A3) as the rank so copying down counts 1, 2, 3 …