PowerPivot BlogRoll – 27th January 2013

Microsoft sneaked a couple of ne

PowerPivot activity announced on Twitter for the week ending 27th January 2013.

 

Chandoo.org
Introduction to DAX Formulas & Measures for PowerPivotbit.ly/126dmx9

Kasper de Jonge PowerPivot Blog
Filtering results to only show top x results in Power View chart – bit.ly/UzkC0n

PowerPivotPro
Modeling Viral and Marketing Growth, Part 3 of 3 – bit.ly/114srjB

Chris Webb’s BI Blog
Building Relative Date Reports in PowerPivotbit.ly/XOEXw8

Stefan Johansson
Create Time Dimension- bit.ly/126aWyJ

PowerPivotPro
Modeling Viral and Marketing Growth, Part Two – bit.ly/XOEBpl

Brent Greenwood’s BI blog
A Time dimension specifically for PowerPivot & AS Tabular – bit.ly/Wut3dK

MindSharp
Demystifying PowerPivot in SharePoint and Excel –http://bit.ly/VkwlNq

PowerPivot BlogRoll – 20th January 2013

Microsoft sneaked a couple of ne

PowerPivot activity announced on Twitter for the week ending 20th January 2013.

 

PowerPivotPro
Modeling Viral Growth vs. Traditional Advertising in PowerPivotbit.ly/10EOrS3

Javier Guillén Pervasive Business Intelligence
Grouping by an Aggregation in DAX – bit.ly/SeC22c

PowerPivotPro
Calendar Tables: Not Just for Formulas. Use Them on Your Pivots Too! – bit.ly/W6YrMa

Javier Guillén Pervasive Business Intelligence
Leveraging DAX query tables for staging Data Transformations – bit.ly/13Awule

Some Random Thoughts
Row Selection Using Slicers in PowerPivot – Part 1 – bit.ly/108bp2H

Chris Webb’s BI Blog
A Different Approach To Last-Ever Non-Empty in DAX – bit.ly/Y82Owx

Excel Do, Dynamic Does
CUBEs For Dessert – Cube formulae with MDX – bit.ly/13uGmNf

SQLBI – Marco Russo
PowerPivot Comatibility across versions – bit.ly/UKJ9MM

SQLBI
Linkback Tables in PowerPivot for Excel 2013 –bit.ly/UBhmAi

DAX, The Dark Knight

Excel Is Superman, DAX Is The Dark Knight

Excel Formulas Are easy, So Why Is DAX So Hard?

Recently, Rob Collie, aka PowerPivotPro, posted a blog entry entitled ‘The Ballad of Ken Puls, DAX Convert’. Whilst it was an interestiung post, this is not to specifically offer any comment on that blog, or to stroke Ken’s ego <g>, I was intrigued by the part where Rob mentioned that Ken had said, and I quote,

 

“I firmly believe that PowerPivot is the future of Excel.  No question in my mind.  It’s insanely easy to get some killer BI in your hands with very little learning, which is awesome.  But…  What I don’t get is DAX.  The light just isn’t going on for me.  If you want to mobilize the Excel Pro army to really make this take off, DAX needs to become accessible for us.”

 

This rang a bell with me, as about six weeks ago I taught a 2 day PowerPivot course with Chris Webb to a class of 15 technology savvy people. These are smart people, but it was clear when covering the more advanced aspects of DAX that they were straining to grasp some of the concepts. On my journey home I was pondering about this. I have stated before that I believe that Microsoft over-egg the simplicity of PowerPivot, suggesting that any user can pick up PowerPivot and create fantastic BI immediately. I think that, whilst easy to get started, to really get the true potential out of PowerPivot you have to have some understanding of Data Modelling.

Have Microsoft got it wrong with DAX as well when they took a deliberate decision to make DAX Excel-like in its syntax? In many ways, this is understandable, their experience of MDX, which really is hard, could weigh heavily on their outlook. But, it has been a double-edged sword in my view. Excel users people are quite ready to give DAX a go because it looks as easy as Excel formulas, but conversely, when they get to advanced DAX (CALCULATE, FILTER, ALL, etc.), they can easily get dis-heartened because it is not as easy as Excel formulas.

So I thought I would look at why DAX is so hard for an Excel user. I came up with 7 topics that I decided to look at, namely:

  • Data Typed
  • Integer Dates
  • Formula Formatting
  • Evaluation Context
  • Extendible
  • Debugging Aids
  • Ease of use

 

Data Typed

Integer Dates

Formula Formatting

Extendible

Debugging Aids

DAX is great if your formula works, but when it doesn’t you need good tools to help you debug it. Unfortunately. Microsoft haven’t provided any debugging tools whatsoever, so debugging is hard, incredibly hard. It should be part of our course, but with no tools to talk about there is nothing to suggest yet.

Just to complicate matters, the data usually consists of thousands, or even hundreds of thousands or millions, of rows, and reducing it to a reduced set is not staright-forward.

Marco Russo, Darren Gosbell, Angry Koala, and Paul te Braak have created a nice Excel addin called DAX Studio which is as good as it gets. it is a useful tool for building DAX formulae, as described by Javier Guillen in his post Using DAX studio to create PowerPivot measures, and this technique can be used to rebuild the formula from scratch checking as you go, but it is not reall a debugging tool, and was not meant as such.

Compare this to Excel. In Excel, the helpful debugging facilities are:

  • Evaluate Formula
  • Watch Window
  • F9

 

The Evaluate Formula facility lets you evaluate a formula in the activecell, stepping through it as each stage of the formula seeing what the result at that stage is, until it is fully resolved. It allows allows stepping in and out of the cell value if the expression being evaluated is a single cell.

The Watch Window allows you to add a cell or a range of cells to be watched, thw window showing the current value, reflecting any changes.

F9 allows you to select any expression within the formula bar and see what it resolves to. The expression can be a single cell, a function, or a set of functions, as long as it is an expression that can be resolved in its own right.

So, three facilties to assist in debugging the formula should make Excel formula debugging easy? In reality, I never use Evaluate Formula or the Watch Window as they do not provide enough value in my view. F9 is very useful, in that it is possible to evaluate any part of the formula. This is in contrast to Evaluate Formula, which evaluates the whole formula which means that the TRUE path is only evaluated, whereas with F9 you can select any part and have it evaluated regardless of whether the actual data would force the formula to follow that path or not.

Okay, Excel has a few tools, which is a few more than DAX, but they are hardly what you would call sophisticated. It still mainly relies on experience, intuition, and insight to be able to debug the more complex Excel formulae. Debugging DAX is diffocult, but debugging complex Excel formulae is not the easiest task either.

Evaluation Context

Evaluation context is probably the

Evaluation context is the aspect of DAX that people struggle with most when they start with PowerPivot. Understanding filter context and row context, when each comes into play, how you can override aspects of the filter context, all of this can seem mind boggling at first.

Ease of use

PowerPivot BlogRoll – 13th January 2013

Microsoft sneaked a couple of ne

PowerPivot activity announced on Twitter for the week ending 13th January 2013.

 

The New Internal Data Model in Excel 2013 – bit.ly/VFumXvk

New Customers Per Day Generalized to “New Customers per Month,” etc. – bit.ly/RP7EeK

PowerPivot User Experience Survey – t.co/aGlhdPu7

New Customers per Day – bit.ly/VJtD7j

Getting the active products between a date range using DAX – bit.ly/VDT7AH

[Update] What is using all that memory on my Analysis server instance? – bit.ly/T51Hsc

Parameterising PowerPivot Connection Strings in Excel 2013 – bit.ly/XKBOTH

Technitrain 2013 Course Schedule: SSAS, PowerPivot, MDX, SSIS, TSQL and SQL Server Engine Training in London – bit.ly/U2mVra

Group Multiple Rows to Single Delimited Row in PowerPivotbit.ly/XiDTlO

CUBEs For Dessert

Microsoft sneaked a couple of ne

Introduction

A few weeks ago I showed how to create a fully dynamic table using CUBE formulae against a PowerPivot model in Cooking With Cubes.This example presented data from the AdventureWorks database of sales of product categories over years, all integrated with a Fiscal Year slicer.

I recently had a similar need, but with the further challenge of presenting two levels in a hierarchy (actually, this was related tables in a snowflake schema). I needed to show all of the items in the first level, and for each of these items, all of the associated items at the second level including those items that have no sales value. The AdventureWorks database has a similar setup with products, where each sale is for a particular product, each product is of specific product sub-category, and each product sub-category is of a specific product category. Within this model, we would be showing sub-categories of Mountain Bikes and Road Bikes against the category of bikes, but not against any other category.

 

I will demonstrate my solution using the same AdventureWorks data. Whilst this will be similar to the previous post in showing how to manage the presentation using Excel formulae, it is extended to multiple data levels and some more complex formatting. Additionally, and the main point of this post, I show a technique to display the related values in that hierarchy, not simple with straight CUBE formulae, by adding some MDX trickery in the CUBE formulae.

Figure 1 shows the final results of all of the formulae and formatting.

Figure 1 – Final Results

Preparing With Good Ingredients

First I need to prepare the PowerPivot model so that I have have product name, product sub-category name, and product category name all on the Product table. To achieve this, I added two calculated columns

[ProductSubcategoryName]
=RELATED(ProductSubcategory[ProductSubcategoryName]
[ProductCategoryName]
=RELATED(ProductCategory[ProductCategoryName]
Formula 1 – PowerPivot Model Calculated Columns

Mixing It Up

The data is ready, so let’s build the required data. I need the following:

  1. a text definition of the cube
  2. a set of all categories
  3. a count if the items in the category set
  4. a list of all of the items in the year set, from the slicer or the dimension if no slicer selections
  5. a count if the items in the year set
  6. a list of all of the items in the category set (in columns)
  7. for each listed category, a set of all associated sub-category items
  8. for each sub-category set, a count of all items in that set
  9. a running total of the sub-category set counts
  10. for each sub-category set, a list of items (in rows)
  11. a reference cell for the sales measure

Taking these in the order given, showing cell(s), defined name, definition:

1. Cube Definition

This is simple text for the connection argument to the cube functions.
J1:[_model]PowerPivot Data
I am using Excel 2010 for this example, Excel 2013 would use the value ThisWorkbookdataModel.
In the previous example, I named this cell _cube, here I use _model.

2. Category Set

This is the set of all categories in the model. This is being taken from the calculated column for product category name in the Products table.
J2:[_setCategory]=CUBESET(_model,”[Product].[ProductCategoryName].Members”,”Categories”)

3. Category Set Count

A simple count of the items in the category set, used in later dynamic formulae.
J3:[_setCategoryCount]=CUBESETCOUNT(_setCategory)-2
I subtract two from the actual count because this set contains the All member and blank, I am not interested in these.

4. Year Set

This is the set of years in the model, constrained by the slicer.
J4:[_setYear]
=IF(CUBERANKEDMEMBER(_model,Slicer_FiscalYear,1)<>”All”,
CUBESET(_model,Slicer_FiscalYear,”Slicer Years”),
CUBESET(_model,”[Date].[FiscalYear].Children”,”Dimension Years”))

As in the previous example, to show all years with no slicer selection (not show All), I check the slicer to see the first set member is All, getting the set from the Date dimsnsion if so, otherwise get the set from the slicer.

6. Year Set Count

A simple count of the items in the year set.
J3:[_setYearCount]=CUBESETCOUNT(_setYear)

6. List of Category Set Items

A list of the items in the category set, for use in building the sub-category sets.
K2:N2:[_listCategory]=IFERROR(CUBERANKEDMEMBER(_model,$J$2,COLUMN()-COLUMN($J$2)+2),””)
The name definition uses a dynamic formula
=OFFSET(_setCategory,0,1,1,_setCategoryCount)
This is a simple dynamic OFFSET formula to build a list of the categories.

7. Sets of Subcategory Items

Sets of sub-category items, one set per category.
K3:N3:[_setsSubcategory]=IFERROR(CUBESET(_model,”EXISTS([Product].[ProductSubcategoryName].Members,”&K$2&”)”,”Products:”&K$2),””)
This is where the MDX magic kicks in. The EXISTS function … Returns the set of tuples of the first set specified that exist with one or more tuples of the second set specified …, so we can use it within a CUBESET function to get all of the product subcategory names for the category above, in _listCategory.
Using this technique, instead of building a set of all subcategories, we build a set of the subcategories for a particular category; in our case the category passed to the CUBESET function from the cells in _listCategory.
I prefix the text shown with the text Products: for readability
The name definition uses a dynamic formula
=OFFSET(_setCategory,1,1,1,_setCategoryCount)

8. List of Subcategory Set Counts

A list of counts for the sub-category sets, for use in the layout.
K4:N4:[_setsSubcategoryCounts]=INDEX(CUBESETCOUNT(_setsSubcategories),COLUMN()-COLUMN(_setCategory))
The name definition uses a dynamic formula
=OFFSET(_setCategory,2,1,1,_setCategoryCount)

9. List of Subcategory Set Count Totals

A list of running totals for the sub-category set counts, for use in the layout.
K5:O5:[_setsSubcategoryTotals]=SUM($K$4:K4)-K$4+1
The name definition uses a dynamic formula
=OFFSET(_setCategory,3,1,1,_setCategoryCount+1)

10. Lists of Sub-category Sets Items

Lists of each set sub-category items, within the relevant category.
K6:N30:[_listSubcategoryies]=IFERROR(CUBERANKEDMEMBER(_model,K$3,ROW()-ROW(_setCategory)-2),””)
This is using the same technique to list the set items as in the previous blog.
The name definition uses a dynamic formula
=OFFSET(_setCategory,4,1,MAX(_setsSubcategoryCounts)-1,_setCategoryCount))
Unlike the other dynamic formulae, this one is multi-column, for the multiple categories.

11. Sales Measure

Finally, we have a cell defining the Sales measure
J7:[_sales]=CUBEMEMBER(_model,”[Measures].[Sum of SalesAmount]”,”Sales”)
This is to save repeated definition within the value formulae.

Figure 2 and Figure 3 below show all of the helper cells populated, and the nanmes defined in the NameManager.

Figure 2 – Populated Helper Cells


Figure 3 – Defined Names

It’s All About The Presentation

All that needs to be done now is to lay it out, allowing for a variable number of categories and a differing variable number of sub-categories within each category.

In my layout, I am repeating each category a number of times equal to to the number of subcategores for that category, but only showing the first instance. The years are shown as column headings, using the year slicer to filter the columns. The values are displayed referencing the category, sub-category, and year headings.

The formulae are as follows:

Category items

A7:An:=IFERROR(INDEX(_listCategory,MATCH(ROW()-ROW(_start),_setsSubcategoryTotals,1)),””)
This matches the ordinal row number within the list of categories against the running totals for sub-category set counts, so as to determine which of the categories we are listing.

 

Sub-category items

B7:Bn:=IFERROR(IF(A7<>A6,””,INDEX(_listSubcategories,COUNTIF($A$7:A7,A7)-1,MATCH(ROW()-ROW(_start),_setsSubcategoryTotals,1))),””)
This uses the same matching technique to determine which sub-category set to list.

 

Filtered Years

D6:H6:=IFERROR(CUBERANKEDMEMBER(_model,_setYear,COLUMN()-COLUMN(_start)-2),””)
Although the slicer controls the selected years, the year set in _setYear outsorts the data, the filtering is done there.

 

Sales Totals

C7:C50:=IFERROR(CUBEVALUE(_model,_sales,IF($A7<>$A6,$A7,$A7:$B7),Slicer_FiscalYear),””)
This determines the sum of the values for the current row, be that just a category row, or a category and sub-category row, whilst respecting the slicer selection. The IF in the formula caters for that possibility of a category row or a category and sub-category row.

 

Sales Values

D7:H50:=IFERROR(CUBEVALUE(_model,_sales,D$6,IF($A7<>$A6,$A7,$A7:$B7)),””)
Similar to the sales totals, but using the year heading instead of the slicer, beacuse the year heading has already take the slicewr into account.

 

Conditional formatting is added to the results to improve the viusual effect and readability.

Product Category Labels

The list of product categories is repeated on every row, changing at the appropriate point of course. This value is needed for the value calculation, but I feel the look is improved if the repeated items are suppressed, so I use conditional formatting to set the font colour to white on repeated items. This uses a simple formula of

A7:A50:=A7=A6

Product Category Totals

I list the product category one more time that its associated sub-categories. This is so that I can show category totals as well as category and sub-category totals. These need highlighting so that the totals stand out. To achieve this, I have made them bold with a blue font, borders above and below. The conditional formatting formula for this is

A7:H50:=AND($A7<>””,$A7<>$A6,ROW($A7)-ROW(_start)-1<=MAX(_setsSubcategoryTotals),COLUMN(A6)-COLUMN(_start)-3<=_setYearCount)

But Is It All Too Rich?

One question that may occur to anyone reading this who is skilled with pivot tables is, why bother? Couldn’t the same effect be achieved by formatting a pivot table showing the product category and sub-category and years with the format set to Show Tabular Form and Repeat All Item Labels? This would give a similar look, but it would only show product categories and sub-categories that have a value, it would not show those that are not selling. Agreed, there is a pivot option to Show items with no data on rows which does address this, but the problem here is that it will show all product sub-categories under EVERY product category, my method only shows the relevant sub-categories.

In Summary

In many ways this is similar to the previous post on CUBE formulae, with an extra level of data and more complex formatting. But what I hope that it shows is that by utilising some fairly straightforward MDX the formulae can do far more complex operations, and extend the data displayed. And who knows how far this can be taken as one’s MDX skills increase?

It is possible that many of the formulas could be replaced with DAX measures in the model, maybe that is a post for another time.

You can download the workbook here

Acknowledgements

Allan Folting of Microsoft first showed me this technique, I am grateful for his insights.

Chris Webb has a series of excellent blog posts on MDX For PowerPivot Users, which are worth reading if this technique interests you.

PowerPivot BlogRoll – 6th January 2013

Microsoft sneaked a couple of ne

PowerPivot activity announced on Twitter for the week ending 6th January 2013.

 

Time To Name This Year’s Book! – bit.ly/TyZ5nX

Database Pros in Demand – bit.ly/UXN78m

[Commercial Webinar] Deep Dive into Business Intelligence in Excel 2013’s PowerPivot and Power View – bit.ly/115ls8J

Creating Linked Tables in PowerPivot for Excel – bit.ly/TyZ5nX

Excel 2013, Power View, Top 10 “long tail” and how DAX helpsbit.ly/YJTMkd

PowerPivot BlogRoll – 30th December 2012

Microsoft sneaked a couple of ne

PowerPivot activity announced on Twitter for the week ending 30th December 2012.

 

Templated Excel workbooks with PowerPivot and Macro’s – bit.ly/12VdWwP

Importing SQL Server Data from Multiple Data Sources into PowerPivot for Excel – bit.ly/UAX22u

PowerPivot – Importing Data from Different Data Sources into PowerPivot for Excel – bit.ly/131Qi0v

End of 2012 and news in 2013 for #PowerPivot, #ssas #tabular and BI – bit.ly/VpwWxI

Refreshing PowerPivot Data in SharePoint 2013 – bit.ly/131Q3Ti

[Video] Using PowerPivot with DataMarket Section of Windows Azure Marketplace – bit.ly/12Vd23e

PowerPivot BlogRoll – 23rd December 2012

PowerPivot activity announced on Twitter for the week ending 23rd December 2012.

 

Using HASONEVALUE in a DAX IF statement – bit.ly/TVMwA7

Create a memory-efficient Data Model using Excel 2013 and the PowerPivot add-in – bit.ly/ZfaRsf

Introduction to MDX for PowerPivot Users, Part 5: MDX Queries – bit.ly/12wLGQI

[Update] What is using all that memory on my Analysis server instance? – bit.ly/T51Hsc

Running Product in DAX: Calculating Portfolio Returns – bit.ly/XOobxz

The CALCULATE Function—New DAX Video Tutoria – bit.ly/XTTirq. Note that this is a subscription site.

Dynamic CUBE Formulae In Excel – bit.ly/R6Fnjs

The Ballad of Ken Puls, DAX Convert – bit.ly/RLv1ac

DAX Spicy Scale Survey – bit.ly/RbtqZC

Cooking With CUBEs

Microsoft sneaked a couple of ne

As we should all know by now, PowerPivot provides Excel with a powerful way to harness data from one or more sources, and to do further analysis on that data within familiar pivot tables.

Furthermore, because PowerPivot is creating an in-memory cube of the data, it is possible to build an analysis using CUBE formulae. I have blogged a couple of times about CUBE formulae, in Cycling Through The Fog and in Cracking The Code.

In Excel, as with any development, you want your solution to be as flexible and dynamic as possible. This blog is about building dynamic tables using CUBE formulae, but to start with the following formula shows an example of a value extracted from a PowerPivot model using CUBE functions

=CUBEVALUE(“PowerPivot Data”,
CUBEMEMBER(“PowerPivot Data”,”[Measures].[Sum of SalesAmount]”),
CUBEMEMBER(“PowerPivot Data”, “[DimProductCategory].[EnglishProductCategoryName].&[Bikes]”),
CUBEMEMBER(“PowerPivot Data”,”[DimDate].[FiscalYear].&[2006]”))

Equation 1

This formula gets the Sales Amount from the PowerPivot cube for the Bikes product category, for the fiscal year 2006. There will be many values at this intersection, there can be many dates in 2006 and many products within that category, all pre-aggregated in the cube; the CUBEVALUE function returns that aggregate amount

We could build the whole table of values using similar formulae. In our table we need to know what the value is related to, so we have row and column headers that identify the intersection points. We could define those headers using the CUBEMEMBER functions giving a table such as shown in Figure 1 below, which shows a table based on AdventureWorks.

Figure 1 – Table of values over year and product category

The formulas for the headings are

=CUBEMEMBER(“PowerPivot Data”,”[DimDate].[FiscalYear].&[2006]”)
=CUBEMEMBER(“PowerPivot Data”,”[DimDate].[FiscalYear].&[2007]”)
etc. for the column headings,

Equation 2

and

=CUBEMEMBER(“PowerPivot Data”, “[DimProductCategory].[EnglishProductCategoryname].&[Accessories]”)
=CUBEMEMBER(“PowerPivot Data”, “[DimProductCategory].[EnglishProductCategoryname].&[Bikes]”)

etc. for the row headings.

The values at the intersection points simply use these heading cells like so

=CUBEVALUE(“PowerPivot Data”,”[Measures].[Sum of SalesAmount]”,$A3,B$1)

This is equivalent to the formula given in Equation 1.

Slicing the Vegetables

Further richness is bestowed upon us because we can also link slicers to our table, giving us the sort of filtering we have with the pivot tables. For example, Figure 2 shows the same data table built using CUBE formulae with a fiscal year slicer; the data reflecting the fact that only the years 2006, 2007, and 2008 have been selected.

Figure 2 – Table of values reflecting years slicer selections

Showing the slicer selections on your report has been covered elsewhere, but it is so useful and asked so often that I thought I would also cover. I also have a couple of variations that I haven’t seen elsewhere, which are worth presenting.

The Menu

Previously, as shown in the formulae in Equation 2, we built the row and column headers using hard-coded values for the year and category fields. We need to be more dynamic in how we list these values. To show the slicer selections as in E5, F5, etc., we need a list of values from which we can choose and display the individual ordered items. The CUBESET function gives us this. The syntax for CUBESET is

CUBESET(connection. set_expression, , [sort_order], [sort_by])

where connection is the cube, set_expression is the set of values required, and caption is a value to display. So, looking at cell D1 we have the formula

=CUBESET(“PowerPivot Data”,Slicer_FiscalYear,”Set of Years

Equation 3

which would look as shown in Figure 3 when added to cell D1 to build our set of fiscal years.

Figure 3 – Slicer years set formula

As can be seen, we use Slicer_FiscalYear as the set_expression, so the set will include all selected values in that slicer, with the caption signifying the cell contents.

So far, so good, but we still need to list those selected values. For this, we use the CUBERANKEDMEMBER function, which returns the nth, or ranked, member in a set. The syntax of this

=CUBERANKEDMEMBER(connection, set_expression, rank, )

where connection is the cube as before, set_expression is the set of values to choose from, and rank is nth value. So, to get the first member, we use

=CUBERANKEDMEMBER(“PowerPivot Data”,$D$1,1)

for the second

=CUBERANKEDMEMBER(“PowerPivot Data”,$D$1,2)

and so on.

Because there are 5 years in the PowerPivot model, and when filtered in the slicer we might be showing less than 5, we need to cater for a variable number of items. The simplest way is just to add an error wrapper around the formula,

=IFERROR(CUBERANKEDMEMBER(“PowerPivot Data”,$D$1,1),””)

=IFERROR(CUBERANKEDMEMBER(“PowerPivot Data”,$D$1,2),””)

etc.

Equation 4

Why Extra Ingredients?

We could nest the CUBESET function within the CUBERANKEDMEMBER,but that would mean that a set is evaluated 5 times. By defining the set in its own cell and referring to that cell within the CUBERANKEDMEMBER function, it is evaluated just the once. A small matter, but it makes the spreadsheet easier to maintain, and is more efficient.

Cooked To Perfection

It’s as simple as that.

But hang on a minute, have we overcooked it?

Looking at the syntax definition for these two functions, we can see that they both take set_expression as an argument. The CUBESET function is passed the slicer values as its set, and in turned is passed to the CUBERANKEDMEMBER function as its set.

As the slicer values is a set_expression, you would think that we should be able to pass the slicer values directly to CUBERANKEDMEMBER as a set and be done with. And so we can, these formulae

=IFERROR(CUBERANKEDMEMBER(“PowerPivot Data”,Slicer_FiscalYear,1),””)

=IFERROR(CUBERANKEDMEMBER(“PowerPivot Data”,Slicer_FiscalYear,2),””)

etc.

Equation 5

work equally as well as those formulae in Equation 4

Managing The Ingredients

The list of product categories can also be listed in a similar way using CUBESET and CUBERANKEDMEMBER. Here we do need CUBESET as there is no pre-defined set of values as we had with the fiscal year that we can pick up. The set will be all values for the Product Category English name in the Product Category table,

=CUBESET(“PowerPivot Data”,
“[DimProductCategory].[EnglishProductCategoryName].Children”,
“Set of Categories”)

Equation 6

As can be seen, .Children gets us all of the category values.

One thing to note is the use of the caption argument. Again, this helps to highlight the cell containing the set.

We now have formulae that can define our full table, such as

D1: the formula in Equation 3
=CUBESET(“PowerPivot Data”,Slicer_FiscalYear,”Set of Years”)

D2: the formula in Equation 6
=CUBESET(“PowerPivot Data”,
“[DimProductCategory].[EnglishProductCategoryName].Children”,
“Set of Categories”)

E5:I5: the formulae in Equation 5
=IFERROR(CUBERANKEDMEMBER(“PowerPivot Data”,Slicer_FiscalYear,1),””)
=IFERROR(CUBERANKEDMEMBER(“PowerPivot Data”,Slicer_FiscalYear,2),””)

etc.,

D6:D9: formulae for the product categories

=IFERROR(CUBERANKEDMEMBER(“PowerPivot Data”,$D$2,1),””)
=IFERROR(CUBERANKEDMEMBER(“PowerPivot Data”,$D$2,2),””)
=IFERROR(CUBERANKEDMEMBER(“PowerPivot Data”,$D$2,1),””)
=IFERROR(CUBERANKEDMEMBER(“PowerPivot Data”,$D$2,2),””)

And finally, in E6:I9, the formulae for the values

=IFERROR(CUBEVALUE(“PowerPivot Data”,”[Measures].[Sum of SalesAmount]”,$D6,E$5),””)
etc., each cell reflecting the correct product category ($D6) and fiscal year (E$5).

Our table now has a full set of values, and reflects the choices made in the fiscal year slicer.

(If we wished, we could add the product categories to a slicer, and make our table dynamically reflect that.)

Ready to Serve?

Although we have been diligent in storing the evaluated sets in one place rather than nest a CUBESET function within the CUBERANKEDMEMBER function, but there are still a number of things going on here that I just don’t like:

  • The connection is hard-coded, multiple times
  • If a new year is added to the data, just copying cell I5 to J5 won’t work because the rank is hard-coded in the formula, it will need a small change
  • If no selection is made in the fiscal year slicer, the values shown are the total of all years, with a header value of All, as shown in Figure 4. This may be what is required in some instances, in others we may want to show each year’s values individually.

Figure 4 – Showing all years as a total with no slicer selections

These ‘difficulties’ can be overcome relatively easily.

Rather than hard-code the connection within each formula, put the connection text ‘PowerPivot data’ (without the quotes), in a cell, say D3, and assign an Excel name _cube, then use that defined name within the formulae. Note that this also makes the transition to Excel 2013 simpler, where the connection has now changed to ‘ThisWorkbookDataModel’ (again, without the quotes).

The rank is managed by using a function that returns a variable number depending upon the row or column of the cell, namely ROW([reference]) or COLUMN([reference]). You might think that you can use COLUMN(A1) in E5, and copy that across so that it updates to COLUMN(B1), COLUMN(C1), etc. Believe me, this is a very bad idea. Although everything will be fine at first, what happens if you decide to insert a column before column E? The answer is that COLUMN(A1) will update to COLUMN(B1) and whereas the first column of year table originally reflected the first selected year in the fiscal year slicer, it will now reflect the second. You might say that you would never do that, but no-one ever does until they do. For the sake of a simple change it is hardly worth risking it.

The suggested change is to use COLUMN()-COLUMN($D$5), which uses the top left cell of our table as an anchor point. Thus, a formula in cell E5 using these functions will return 1 for that calculation, and so on. If a column is inserted to the left of the table, those parts of formulae will update to COLUMN()-COLUMN($E$5), which means the formula that was in cell E5 which has now moved to cell F5 still return 1 for that calculation.

Similarly, the category list will use ROW()-ROW($D$5).

Finally, how can we show each year in the column headers and the vales for those years when no slicer selection is made, rather than showing ‘All’ and totals for all years? We already have the formula in D1 that gets the set of selected slicer years, that is

=CUBESET(_cube,Slicer_FiscalYear,”Slicer Years”)

As we showed before, we can get a set of all year regardless of slicer selection with the CUBESET function and the member’s children property, as in

CUBESET(_cube,”[DimDate].[FiscalYear].Children”,”Dimension Years”)

But how do we know when to use which? One way would be to test whether the first member of this slicer set returns All. If it does, there are no slicer selections so we show all years individually, if not we show the slicer selected years. We can check the first slicer set value with the following

IF(CUBERANKEDMEMBER(_cube,Slicer_FiscalYear,1)<>”All”, …

Adding all three elements together, we have the following formula in D1 that determines what goes into the set of years that will drive the table column headings

=IF(CUBERANKEDMEMBER(_cube,Slicer_FiscalYear,1)<>”All”,
CUBESET(_cube,Slicer_FiscalYear,”Slicer Years”),
CUBESET(_cube,”[DimDate].[FiscalYear].Children”,”Dimension Years”))

With this formula to get the years set, we can see all the years listed when no selections are made on the ribbon rather than showing all year totals, as in Figure 5.

Figure 5 – Showing all years with no slicer selections

What’s For Dessert?

That’s about it. Using this technique we have a table that shows the value by year by product category, with a slicer for selecting specific years which is reflected in the years shown in the table. The years and product categories are dynamically built and so can accommodate extra years and extra categories in the source data, and the years can also handle a full slicer set without showing the values as totals for all years.

PowerPivot BlogRoll – 16th December 2012

PowerPivot in Excel 2013 – bit.ly/V1jN01

How PowerPivot can help financial reporting –bit.ly/T0o1Dj

Importing your SalesForce data directly into PowerPivotbit.ly/T0nRMi

Set Up a VM Test Server for PowerView and PowerPivotbit.ly/R0QY3s

Hide Calculated Items With Zero Totals In PowerPivot PivotTables – bit.ly/12tBW8W

Book Review: DAX Formulas For PowerPivotbit.ly/12hDDXR

Creating a Spacer Column in a PowerPivot PivotTable – bit.ly/T2bMHu