“OK, I want the brooding frontman in the foreground, and the keyboard player and drum machine programmer in the back.”

-Photographer for the EuroSynth band, MDX

I’ve always loved this photo.  Dany Hoter, master of the analysis arts, takes his rightful place in the foreground.  Pierre-Henri and I, lurking in the back with mirthful smirks.

It always looked to me like the back cover of a cd booklet – this is kinda what I expected the Chemical Brothers to look like.  Alas, there is no cd, and no band.  This was taken in 2003 by Allan Folting (the Pivot Master) while the four of us were awaiting a hydrofoil car ferry (!) in Denmark.  (Post-production effects a la U2’s “Unforgettable Fire” provided by Dave Gainer, former Group Program Manager for Excel).

Today folks, we are honored to have Dany, the world’s leading authority on cube formulas, finish the lesson that I began.  A fitting conclusion indeed.

(For more information on Dany, one of my favorite human beings, be sure to check out his profile).

### Take it away, Dany…

In the last chapter of the Fantasy Football case, Rob introduced the Excel cube functions. These 7 functions were introduced in Excel 2007 and are not directly related to PowerPivot. The cube functions purpose was to enable data from OLAP cubes to be brought into Excel cells using functions and thus enable any shape of report combining other Excel formulas, data from different cubes in one report etc.

Because PowerPivot create a data source which is compatible with OLAP cubes it can be consumed by these functions and open up many interesting scenarios.

In Rob’s post he used cubeset to generate the children of a member and to show the top N members in the set using a measure.

In the comments that followed Rob’s post people were asking about allowing filters on other dimensions while calculating the top N members in the set.

It is probably easiest if you go ahead an open up the workbook I created, located here, and then follow along below.

### How it works

The only thing we need to keep in mind in to achieve our goal is to make sure that the order of the members in the set is by descending order of the measure combined by the filters. If you want to report on the top 10 products in June and show how they are selling in July , you need to make sure to get the top 10 based on sales and June. May be you want also to use a specific geography and report on the top 10 products in Canada in June.

The way to sort a set by a combination of values from different dimensions is by creating a tuple which is in MDX terminology a collection of single members , each from a different attribute(Or hierarchy). Sales of Ice cream in Seattle in June is a tuple with three members in it. A tuple can be created by reusing one the functions we already met in the original post – cubemember.

If you supply a range of cells to cubemember instead of just one it will create a tuple from all the members represented by the cells. It is your responsibility to make sure that each cell in the range contains a valid member and that they are all from different fields/columns.

In the attached example you can see such an example based on AdventureWorks data.

You see three slicers, on Country year and month. Notice that I use the month number and not the month name because I haven’t found a way to sort the months by their number value while showing their names.

In the upper box you can decide how to create the top 10 product report. You can choose between the selected month sales and the previous month. You can also choose between using just the measure value or a tuple which is created from the values of the filtered members in the slicers + the measure. In order to use the filtered member I need to extract the first member from the slicer set using cuberankedmember. A set is not a valid argument to cubemember.

So the formula to extract the first member from the countries filter is =CUBERANKEDMEMBER("Sandbox",Slicer_EnglishCountryRegionName,1)

And the formula using to define the tuple is =CUBEMEMBER("Sandbox",(O3,O4,O6,IF(L3="Selected Month",P5,O5)),"Tuple")

P5 extracts the filtered month while O5 contains the following formula =CUBEMEMBER("Sandbox","[Time].[MonthNumberOfYear].["&P5&"].lag(1)")

The MDX function lag(1) returns the previous member of a given member.

The solution has one main flaw: it cannot guarantee the right order and thus the true top 10 products if the user selects more than one member in any one of the filters. The reason is that multiple members cannot participate in the tuple creation and so only the first member in the filter set can be used. In the example a message is shown if the user selects multiple members .

We can be even more strict and blank the report completely if the value of cell Q3 is >1. This cell contains the formula =CUBESETCOUNT(Slicer_CalendarYear)*CUBESETCOUNT(Slicer_EnglishCountryRegionName)*CUBESETCOUNT(Slicer_MonthNumberOfYear)

Remember that each slicer returns a set with the filtered members and so can be consumed by cubesetcount and cuberankedmember. The same is true for any page field in a pivot.

The set in a page field is referenced by its cell address while the set in a slicer by using the slicer’s name.

I use conditional formatting to show only rows that are not empty and to show a border for the non-blank rows.

#### Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology.

## This Post Has 10 Comments

1. Thanks Rob en Dany great stuff. Takes some while for me to digest and try out but thanks for sharing.

2. milang says:

This is very very good stuff and useful, thank you very much!!!

3. milang says:

Is there a possibility to replace references in this formula
=CUBEMEMBER(“Sandbox”,(O3,O4,O6,IF(L3=”Selected Month”,P5,O5)),”Tuple”)
so you don`t have to use cells O3,O4…
something like this
=CUBEMEMBER(“Sandbox”,(CUBERANKEDMEMBER(“Sandbox”;Slicer_CalendarYear;1),CUBERANKEDMEMBER(“Sandbox”;Slicer_EnglishCountryRegionName;1)…

4. Mark says:

Old post but landed on it looking for something, maybe will help someone…

From above “Notice that I use the month number and not the month name because I haven’t found a way to sort the months by their number value while showing their names.”

In Power Pivot say you have 2 columns, Value and String, you want string sorted by value, select String column, select ‘sort by Column’ on the ribbon and point sort by at Value.

Thus month number is your sort for Month name.

1. powerpivotpro says:

Good point Mark! Yes, this post is so old that the feature in question you’re suggesting we use did not exist back then! GREAT catch! 🙂

1. kathy says:

I’m new to PowerPivot and trying to find out before I spend days/weeks learning the program if my desired result is even possible. I’m hoping for a cube type environment where I can click and drill down in place. The data I’m using is only approx 5,000 rows and all contained in a single sheet. With a dozen or so levels of data. Is this doable without SQL Server’s Analysis Services?

1. powerpivotpro says:

Hi Kathy. I’m not sure precisely what you are asking, but yes, PowerPivot runs on your desktop, it’s free, and it IS actually Analysis Services – just “reskinned” to be more friendly to Excel folks. PowerPivot IS a cube, in other words.

5. Hi Rob,

Thanks for showing it to us. However, I was not able to open the example file. Can you please update it?

Tran Tran

6. Diego says:

Hi Rob this is quite interesting thank you very much for sharing.

I used your trick to produce a TOP 10 products list based on Sales and Gross Profit %. For each product I am also retrieving the product description on a separate column. I added in a slicer to be able to produce the report on a daily basis.

Everything works fine, the only issue is performance: when I slice by date, the report takes between 4 to 5 minutes to refresh. Excel says “query is running in background”

I am running the report on 91K rows. I used power query (fastload) to load the model into power pivot and then cubeformulas to produce the report. The TOP 10 product list is produced entering a sales measure on the argument “sort by” in the cubeset function. I then use cuberanked to get the TOP 10 list.

For testing purposes I reduced the number of rows from 91K to 21K and the refresh time went down by 50% to 2 minutes. This is much better but still far away from the standard 3 to 5 secs I usually wait when I use cube formulas.

Could it be that the argument “sort by” on the sales measure is cutting down the performance?

7. Julian Chen says:

Could you please update the workbook to the version of Excel 2013? Thanks.