Excel Do, Dynamic Does : PowerPivotivot, Excel 2010http://msmvps.com/blogs/xldynamic/archive/tags/PowerPivotivot/Excel+2010/default.aspxTags: PowerPivotivot, Excel 2010enCommunityServer 2008.5 SP2 (Build: 40407.4157)CUBEs For Desserthttp://msmvps.com/blogs/xldynamic/archive/2013/01/13/cubes-for-dessert.aspxSun, 13 Jan 2013 13:07:00 GMTd67277c4-116b-43f1-b688-e9ef184ea916:1822576Bob Phillips0http://msmvps.com/blogs/xldynamic/rsscomments.aspx?PostID=1822576http://msmvps.com/blogs/xldynamic/archive/2013/01/13/cubes-for-dessert.aspx#comments<p>
</p>
<h2>Introduction</h2>
<p>A few weeks ago I showed how to create a fully dynamic table using CUBE formulae against a PowerPivot model in <a href="http://msmvps.com/blogs/xldynamic/archive/2012/12/16/cooking-with-cubes.aspx">Cooking With Cubes</a>.This example presented data from the AdventureWorks database of sales of product categories over years, all integrated with a Fiscal Year slicer.</p>
<p>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.</p>
<p> </p>
<p>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.</p>
<p><strong>Figure 1</strong> shows the final results of all of the formulae and formatting.</p>
<p><a href="http://msmvps.com/cfs-file.ashx/__key/CommunityServer.Blogs.Components.WeblogFiles/xldynamic/3276.Final-Results.png"><img border="0" src="http://msmvps.com/resized-image.ashx/__size/550x0/__key/CommunityServer.Blogs.Components.WeblogFiles/xldynamic/3276.Final-Results.png" alt="" /></a></p>
<p class="figure">Figure 1 - Final Results</p>
<h2>Preparing With Good Ingredients</h2>
<p>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</p>
<p>[ProductSubcategoryName]<br />
<span class="function"><strong>=RELATED(ProductSubcategory[ProductSubcategoryName]</strong></span><br />
[ProductCategoryName]<br />
<span class="function"><strong>=RELATED(ProductCategory[ProductCategoryName]</strong></span><br />
<strong>Formula 1 - PowerPivot Model Calculated Columns</strong></p>
<h2>Mixing It Up</h2>
<p>The data is ready, so let's build the required data. I need the following:</p>
<ol>
<li>a text definition of the cube</li>
<li>a set of all categories</li>
<li>a count if the items in the category set</li>
<li>a list of all of the items in the year set, from the slicer or the dimension if no slicer selections</li>
<li>a count if the items in the year set</li>
<li>a list of all of the items in the category set (in columns)</li>
<li>for each listed category, a set of all associated sub-category items</li>
<li>for each sub-category set, a count of all items in that set</li>
<li>a running total of the sub-category set counts</li>
<li>for each sub-category set, a list of items (in rows)</li>
<li>a reference cell for the sales measure</li>
</ol>
<p>Taking these in the order given, showing cell(s), defined name, definition:</p>
<h3>1. Cube Definition</h3>
<blockquote>
<p>This is simple text for the connection argument to the cube functions.<br />
<strong>J1:[_model]PowerPivot Data</strong><br />
I am using Excel 2010 for this example, Excel 2013 would use the value <i>ThisWorkbookdataModel</i>.<br />
In the previous example, I named this cell <strong>_cube</strong>, here I use <strong>_model</strong>.</p>
</blockquote>
<h3>2. Category Set</h3>
<blockquote>
<p>This is the set of all categories in the model. This is being taken from the calculated column for product category name in the <strong><i>Products</i></strong> table.<br />
<strong>J2:[_setCategory]=CUBESET(_model,"[Product].[ProductCategoryName].Members","Categories")</strong></p>
</blockquote>
<h3>3. Category Set Count</h3>
<blockquote>
<p>A simple count of the items in the category set, used in later dynamic formulae.<br />
<strong>J3:[_setCategoryCount]=CUBESETCOUNT(_setCategory)-2</strong><br />
I subtract two from the actual count because this set contains the All member and blank, I am not interested in these.</p>
</blockquote>
<h3>4. Year Set</h3>
<blockquote>
<p>This is the set of years in the model, constrained by the slicer.<br />
<strong>J4:[_setYear]<br />
=IF(CUBERANKEDMEMBER(_model,Slicer_FiscalYear,1)<>"All",<br />
CUBESET(_model,Slicer_FiscalYear,"Slicer Years"),<br />
CUBESET(_model,"[Date].[FiscalYear].Children","Dimension Years"))</strong><br />
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.</p>
</blockquote>
<h3>6. Year Set Count</h3>
<blockquote>
<p>A simple count of the items in the year set.<br />
<strong>J3:[_setYearCount]=CUBESETCOUNT(_setYear)</strong></p>
</blockquote>
<h3>6. List of Category Set Items</h3>
<blockquote>
<p>A list of the items in the category set, for use in building the sub-category sets.<br />
<strong>K2:N2:[_listCategory]=IFERROR(CUBERANKEDMEMBER(_model,$J$2,COLUMN()-COLUMN($J$2)+2),"")</strong><br />
The name definition uses a dynamic formula<br />
<strong>=OFFSET(_setCategory,0,1,1,_setCategoryCount)</strong><br />
This is a simple dynamic <strong>OFFSET</strong> formula to build a list of the categories.</p>
</blockquote>
<h3>7. Sets of Subcategory Items</h3>
<blockquote>
<p>Sets of sub-category items, one set per category.<br />
<strong>K3:N3:[_setsSubcategory]=IFERROR(CUBESET(_model,"EXISTS([Product].[ProductSubcategoryName].Members,"&K$2&")","Products:"&K$2),"")</strong><br />
This is where the MDX magic kicks in. The <strong>EXISTS</strong> function ... <i>Returns the set of tuples of the first set specified that exist with one or more tuples of the second set specified</i> ..., so we can use it within a <strong>CUBESET</strong> function to get all of the product subcategory names for the category above, in <i>_listCategory</i>.<br />
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 <strong>CUBESET</strong> function from the cells in <strong>_listCategory</strong>.<br />
I prefix the text shown with the text <strong>Products:</strong> for readability<br />
The name definition uses a dynamic formula<br />
<strong>=OFFSET(_setCategory,1,1,1,_setCategoryCount)</strong></p>
</blockquote>
<h3>8. List of Subcategory Set Counts</h3>
<blockquote>
<p>A list of counts for the sub-category sets, for use in the layout.<br />
<strong>K4:N4:[_setsSubcategoryCounts]=INDEX(CUBESETCOUNT(_setsSubcategories),COLUMN()-COLUMN(_setCategory))</strong><br />
The name definition uses a dynamic formula<br />
<strong>=OFFSET(_setCategory,2,1,1,_setCategoryCount)</strong></p>
</blockquote>
<h3>9. List of Subcategory Set Count Totals</h3>
<blockquote>
<p>A list of running totals for the sub-category set counts, for use in the layout.<br />
<strong>K5:O5:[_setsSubcategoryTotals]=SUM($K$4:K4)-K$4+1</strong><br />
The name definition uses a dynamic formula<br />
<strong>=OFFSET(_setCategory,3,1,1,_setCategoryCount+1)</strong></p>
</blockquote>
<h3>10. Lists of Sub-category Sets Items</h3>
<blockquote>
<p>Lists of each set sub-category items, within the relevant category.<br />
<strong>K6:N30:[_listSubcategoryies]=IFERROR(CUBERANKEDMEMBER(_model,K$3,ROW()-ROW(_setCategory)-2),"")</strong><br />
This is using the same technique to list the set items as in the previous blog. <br />
The name definition uses a dynamic formula<br />
<strong>=OFFSET(_setCategory,4,1,MAX(_setsSubcategoryCounts)-1,_setCategoryCount))</strong><br />
Unlike the other dynamic formulae, this one is multi-column, for the multiple categories.</p>
</blockquote>
<h3>11. Sales Measure</h3>
<blockquote>
<p>Finally, we have a cell defining the Sales measure<br />
<strong>J7:[_sales]=CUBEMEMBER(_model,"[Measures].[Sum of SalesAmount]","Sales")</strong><br />
This is to save repeated definition within the value formulae.</p>
</blockquote>
<p><strong>Figure 2</strong> and <strong>Figure 3</strong> below show all of the helper cells populated, and the nanmes defined in the NameManager.</p>
<p><a href="http://msmvps.com/cfs-file.ashx/__key/CommunityServer.Blogs.Components.WeblogFiles/xldynamic/3731.Excel-Helper-Cells.png"><img border="0" src="http://msmvps.com/resized-image.ashx/__size/550x0/__key/CommunityServer.Blogs.Components.WeblogFiles/xldynamic/3731.Excel-Helper-Cells.png" alt="" /></a></p>
<p><strong>Figure 2 - Populated Helper Cells</strong></p>
<p><strong><a href="http://msmvps.com/cfs-file.ashx/__key/CommunityServer.Blogs.Components.WeblogFiles/xldynamic/0257.Name-Definitions.png"><img border="0" src="http://msmvps.com/resized-image.ashx/__size/550x0/__key/CommunityServer.Blogs.Components.WeblogFiles/xldynamic/0257.Name-Definitions.png" alt="" /></a><br /></strong></p>
<p><strong>Figure 3 - Defined Names</strong></p>
<h2>It's All About The Presentation</h2>
<p>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.</p>
<p>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.</p>
<p>The formulae are as follows:</p>
<p><strong>Category items</strong></p>
<blockquote>
<strong>A7:An:=IFERROR(INDEX(_listCategory,MATCH(ROW()-ROW(_start),_setsSubcategoryTotals,1)),"")</strong><br />
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.
<p> </p>
</blockquote>
<p><strong>Sub-category items</strong></p>
<blockquote>
<strong>B7:Bn:=IFERROR(IF(A7<>A6,"",INDEX(_listSubcategories,COUNTIF($A$7:A7,A7)-1,MATCH(ROW()-ROW(_start),_setsSubcategoryTotals,1))),"")</strong><br />
This uses the same matching technique to determine which sub-category set to list.
<p> </p>
</blockquote>
<p><strong>Filtered Years</strong></p>
<blockquote>
<strong>D6:H6:=IFERROR(CUBERANKEDMEMBER(_model,_setYear,COLUMN()-COLUMN(_start)-2),"")</strong><br />
Although the slicer controls the selected years, the year set in <strong>_setYear</strong> outsorts the data, the filtering is done there.
<p> </p>
</blockquote>
<p><strong>Sales Totals</strong></p>
<blockquote>
<strong>C7:C50:=IFERROR(CUBEVALUE(_model,_sales,IF($A7<>$A6,$A7,$A7:$B7),Slicer_FiscalYear),"")</strong><br />
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 <strong>IF</strong> in the formula caters for that possibility of a category row or a category and sub-category row.
<p> </p>
</blockquote>
<p><strong>Sales Values</strong></p>
<blockquote>
<strong>D7:H50:=IFERROR(CUBEVALUE(_model,_sales,D$6,IF($A7<>$A6,$A7,$A7:$B7)),"")</strong><br />
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.
<p> </p>
</blockquote>
<p>Conditional formatting is added to the results to improve the viusual effect and readability.</p>
<p><strong>Product Category Labels</strong></p>
<blockquote>
<p>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 </p>
<p><strong>A7:A50:=A7=A6</strong></p>
</blockquote>
<p><strong>Product Category Totals</strong></p>
<blockquote>
<p>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</p>
<p><strong>A7:H50:=AND($A7<>"",$A7<>$A6,ROW($A7)-ROW(_start)-1<=MAX(_setsSubcategoryTotals),COLUMN(A6)-COLUMN(_start)-3<=_setYearCount)</strong></p>
</blockquote>
<h2>But Is It All Too Rich?</h2>
<p>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 <i>Show Tabular Form</i> and <i>Repeat All Item Labels</i>? 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 <i>Show items with no data on rows</i> which does address this, but the problem here is that it will show all product sub-categories under <span style="text-decoration:underline;">EVERY</span> product category, my method only shows the relevant sub-categories.</p>
<h2>In Summary</h2>
<p>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?</p>
<p>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.</p>
<p>You can download the workbook here <a href="http://dl.dropbox.com/u/4911716/xld.CUBE%20Formulae%20-%20Hierarchy.xlsx"><img border="0" src="http://msmvps.com/resized-image.ashx/__size/550x0/__key/CommunityServer.Blogs.Components.WeblogFiles/xldynamic/0677.download.gif" alt="" /></a>
</p>
<h2>Acknowledgements</h2>
<p>Allan Folting of Microsoft first showed me this technique, I am grateful for his insights.</p>
<p>Chris Webb has a series of excellent blog posts on <a href="http://bit.ly/Xwug2T">MDX For PowerPivot Users</a>, which are worth reading if this technique interests you.</p><div style="clear:both;"></div><img src="http://msmvps.com/aggbug.aspx?PostID=1822576" width="1" height="1">Excel 2010MDXDAXExcel 2013PowerPivotivotCUBE Formulae