CUBERANKEDMEMBER Function

Returns the member at a given position in a set — perfect for top-N lists.

Category

Cube

Available in

Excel 2007+ for Windows, Microsoft 365

Returns

The nth member of a set

Overview

CUBERANKEDMEMBER picks the 1st, 2nd, 3rd… member from a set. With a set sorted by sales, that's a live Top 5 that re-ranks itself every time the data refreshes.

Point CUBEVALUE at each ranked member to show its number beside it.

Syntax

=CUBERANKEDMEMBER(connection, set_expression, rank, [caption])

Arguments

ArgumentRequired?What it does
connectionYesThe connection name in quotes. For the workbook's own Data Model it's "ThisWorkbookDataModel"; for an Analysis Services cube, use the name shown in Data › Queries & Connections.
set_expressionYesUsually a cell holding a CUBESET, or set text such as "{[Sales].[Region].&[North],[Sales].[Region].&[South]}".
rankYesWhich position to return: 1 is the first member of the set.
captionOptionalText to show instead of the member's name.

Examples

Example 1 — Top 3 products

B1 holds a CUBESET of products sorted by Total Sales (descending). Column A holds the rank numbers.

B3fx=CUBERANKEDMEMBER("ThisWorkbookDataModel",$B$1,A3)
ABC
1SetProducts
2RankProductTotal Sales
31Toner182,400
42Paper141,950
53Pens96,300
Illustrative values from Northstar's Data Model.
=CUBERANKEDMEMBER("ThisWorkbookDataModel",$B$1,A3) Explain

Column C uses =CUBEVALUE("ThisWorkbookDataModel","[Measures].[Total Sales]",B3).

Common mistakes

  • Errors or empty cells at the bottom of the list. The rank is larger than the number of members. Check with CUBESETCOUNT first.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.