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 🙂
Pingback: Excel Cubefunctions: Trick for dynamic sets – The BIccountant | CompkSoft
Pingback: Cubemember and Cubeset problem
Aadhaar download process is now very simple and fast. You can easily get your card online using Aadhaar number or OTP verification without visiting any center or office.
e Shram portal helps unorganized workers register easily and get benefits. The process is simple, and every worker should complete registration to access government schemes and future support.
EPFO login allows users to manage their PF account online. You can check balance, download passbook, and track claims easily without visiting any office or facing delays.
Many students across the state are now using the ekalyan portal to apply for scholarships and check their payment status online without visiting any government office.
That contrast between your old hidden-Pivot workaround and Andrew Todd’s more elegant set-based approach lands nicely, because you’re not chasing cleverness for its own sake, you’re trying to keep the model flexible