CUBESET function
Defines a group of members (for example every product) that CUBERANKEDMEMBER and CUBESETCOUNT can work with — handy for Top-10 lists.
Syntax
=CUBESET(connection, set_expression, [caption], [sort_order], [sort_by])
How to use CUBESET in Excel, step by step
CUBESET 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
- 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.
- Define the set. Type =CUBESET("ThisWorkbookDataModel","[Sales].[Product].Children","All products",2,"[Measures].[Sum of Amount]") — sort_order 2 means largest first by the measure.
- List the top items. Below it, use =CUBERANKEDMEMBER("ThisWorkbookDataModel",$B$1,1), then 2, 3 … to list the top products.
- Count the set. Use =CUBESETCOUNT(B1) to see how many members the set holds.
Example
=CUBESET("ThisWorkbookDataModel","[Sales].[Product].Children","All products",2,"[Measures].[Sum of Amount]")
Result: All products (a set, sorted by sales)
sort_order: 0 none, 1 ascending, 2 descending, 3 alpha ascending, 4 alpha descending, 5 natural ascending, 6 natural descending.