Excel Cubefunctions: Trick for dynamic sets

Today I read Andrew Todd’s brilliant guest post on Powerpivotpro.com about some really cool tricks on cubefunctions. The second one shows a very elegant solution to what I’ve formerly done with my “hidden-Pivot”-technique: Avoid the cubeset-functions by referencing a hidden pivot table instead. So if the set of “cubeslices” you want to use in your reports is static, this is the most elegant way to go in my eyes.

But if it is dynamic, like in a Top-10 ranking or similar, this technique still has it’s reason of being: Say you want to show a section in your dashboards that shows top-x elements, a calculation of their share on the total, then an aggregation on all others and a sum on all like this:

The MDX for the creation of a cubeset that includes all your slicer selections in your reports is a real experts topic (link) – well above us Excel-mortals I think. So instead I’m creating a “shadow-Pivot” that is connected to my slicers and will therefore always return the matching Top-10-elements for the respective selections. So this pivot will include the relevant measures as well:

In order keep users away from trying to unhide this sheet and look what’s in there, I check the row section and give it a technical name, like: “Cubeset” 🙂

You can adress this named range in similar way to CUBERANKEDMEMBERS: INDEX(Cubeset, 1) for the first, so INDEX(Cubeset, n) for the others then.

You can download the file here: CubeSetAlternatvie.xlsx

& celebrate Excel to be and stay our tool No1 🙂