• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Applying Slicers to CUBESET / CUBERANKEDMEMBER

C

Christof

Member
Hi,
I'm trying to achieve a list of my best selling product based on criteria from Slicers selections.
I made a Named Range of all my Slicers ("Slicers1") that applies CUBESET to each slicer name.

Ive then created a Sort of top selling tours by doing this formula in cell $Z$2:

=CUBESET("ThisWorkbookDataModel",
"[ToursTable].[Tour Description].children",
"Tour Description",
2,
"[Measures].[Sum of Pax]")

Then I wanted to start my list by doing this:

=CUBERANKEDMEMBER("ThisWorkbookDataModel",$Z$2,ROW(A1))

I was hoping i could have made it:
=CUBERANKEDMEMBER("ThisWorkbookDataModel",$Z$2,ROW(A1),Slicers1)
but it doesnt work.

Any advice?

Thanks
Chris
 
Without sample file, bit hard to help you.

However, if you need to use slicer selection in CUBE functions, you'll need bit more set up.
1. Single selection on single slicer only.
You'll need a cell to hold selected value from slicer. See link below for basic set up.
http://www.ashishmathur.com/tag/cuberankedmember/

2. Multiple selection, Multiple slicer.
This will be rather complex, if you don't use underlying pivottable in hidden sheet. If you are able to have hidden sheet, read link below.
https://powerpivotpro.com/2014/04/cross-filtering-in-slicers-with-cube-formulas/

If you can't... then how you build it will significantly differ between Excel versions.
 
Any news about this topic¿? If you can do it on Pivot tables I believe it's possible with cube functions.
Thanks in advance for your comments.
Regards,
 
You must log in or register to reply here.
Back
Top