Excel like a finance pro.
Cube function
CUBESET
Defines a calculated set of members from an OLAP cube and sends it to the server.
When to use it
Defines a set of members with an MDX expression, optionally sorted by a measure, and sends it to the server. Other cube functions then work on the set.
The shape of it
- Syntax
=CUBESET(connection, set_expression, [caption], [sort_order], [sort_by])
Worked examples
Sorted set
=CUBESET("Sales","[Product].[Product].Children","All products",2,"[Measures].[Sales]") → All products
Every product, sorted descending by sales. The cell shows the caption.
Explicit set
=CUBESET("Sales","{[Region].[West],[Region].[East]}","Coasts") → Coasts
An explicit list of members.
Top N
=CUBESET("Sales","TopCount([Product].[Product].Children,5,[Measures].[Sales])","Top 5") → Top 5
Only the top five by sales.
Worth knowing
- Sort orders: 0 none, 1 ascending, 2 descending, 3 alpha ascending, 4 alpha descending, 5 natural ascending, 6 natural descending.
- CUBESETCOUNT and CUBERANKEDMEMBER consume the set.
- Use a caption so the cell is readable.
Where it goes wrong
- #N/A for an invalid MDX expression or while refreshing.
- Large sets slow the workbook.
Related
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.