Excel Do, Dynamic Does : formulas, Data Analysishttp://msmvps.com/blogs/xldynamic/archive/tags/formulas/Data+Analysis/default.aspxTags: formulas, Data AnalysisenCommunityServer 2008.5 SP2 (Build: 40407.4157)Cooking With CUBEshttp://msmvps.com/blogs/xldynamic/archive/2012/12/16/cooking-with-cubes.aspxSun, 16 Dec 2012 22:31:00 GMTd67277c4-116b-43f1-b688-e9ef184ea916:1821046Bob Phillips15http://msmvps.com/blogs/xldynamic/rsscomments.aspx?PostID=1821046http://msmvps.com/blogs/xldynamic/archive/2012/12/16/cooking-with-cubes.aspx#comments<p>
</p>
<p>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.</p>
<p>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 <a href="http://msmvps.com/blogs/xldynamic/archive/2010/01/07/cycling-through-the-fog.aspx">Cycling Through The Fog</a> and in <a href="http://msmvps.com/blogs/xldynamic/archive/2010/10/04/cracking-the-code.aspx">Cracking The Code</a>.</p>
<p>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</p>
<p class="function">=CUBEVALUE("PowerPivot Data",<br />
CUBEMEMBER("PowerPivot Data","[Measures].[Sum of SalesAmount]"),<br />
CUBEMEMBER("PowerPivot Data", "[DimProductCategory].[EnglishProductCategoryName].&[Bikes]"),<br />
CUBEMEMBER("PowerPivot Data","[DimDate].[FiscalYear].&[2006]"))</p>
<p class="equation"><strong>Equation 1</strong></p>
<p>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</p>
<p>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 <span class="function">CUBEMEMBER</span> functions giving a table such as shown in <strong>Figure 1</strong> below, which shows a table based on AdventureWorks.</p>
<p class="centre"><a href="http://msmvps.com/cfs-file.ashx/__key/CommunityServer.Blogs.Components.WeblogFiles/xldynamic/3264.Table-of-values-over-year-over-product-category.png"><img border="0" src="http://msmvps.com/resized-image.ashx/__size/550x0/__key/CommunityServer.Blogs.Components.WeblogFiles/xldynamic/3264.Table-of-values-over-year-over-product-category.png" alt="" /></a></p>
<p class="figure"><strong>Figure 1 - Table of values over year and product category</strong></p>
<p>The formulas for the headings are</p>
<p class="function">=CUBEMEMBER("PowerPivot Data","[DimDate].[FiscalYear].&[2006]")<br />
=CUBEMEMBER("PowerPivot Data","[DimDate].[FiscalYear].&[2007]")<br />
etc. for the column headings, </p>
<p class="equation"><strong>Equation 2</strong></p>
<p>and</p>
<p><span class="function">=CUBEMEMBER("PowerPivot Data", "[DimProductCategory].[EnglishProductCategoryname].&[Accessories]")<br />
=CUBEMEMBER("PowerPivot Data", "[DimProductCategory].[EnglishProductCategoryname].&[Bikes]")</span><br />
etc. for the row headings.</p>
<p>The values at the intersection points simply use these heading cells like so</p>
<p><span class="function">=CUBEVALUE("PowerPivot Data","[Measures].[Sum of SalesAmount]",$A3,B$1)</span></p>
<p>This is equivalent to the formula given in <strong>Equation 1</strong>.</p>
<h2>Slicing the Vegetables</h2>
<p>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.</p>
<p class="centre"><a href="http://msmvps.com/cfs-file.ashx/__key/CommunityServer.Blogs.Components.WeblogFiles/xldynamic/0871.Table-reflecting-year_2700_s-slicer-selections.png"><img border="0" src="http://msmvps.com/resized-image.ashx/__size/550x0/__key/CommunityServer.Blogs.Components.WeblogFiles/xldynamic/0871.Table-reflecting-year_2700_s-slicer-selections.png" alt="" /></a></p>
<p class="figure"><strong>Figure 2 - Table of values reflecting years slicer selections</strong></p>
<p>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.</p>
<h2>The Menu</h2>
<p>Previously, as shown in the formulae in <strong>Equation 2</strong>, 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 <strong>CUBESET</strong> function gives us this. The syntax for <strong>CUBESET</strong> is</p>
<p><span class="functionsyntax"><i>CUBESET(connection. set_expression, [caption], [sort_order], [sort_by])</i></span></p>
<p>where <span class="functionsyntax"><i>connection</i></span> is the cube, <span class="functionsyntax"><i>set_expression</i></span> is the set of values required, and <span class="functionsyntax"><i>caption</i></span> is a value to display. So, looking at cell D1 we have the formula</p>
<p><span class="function">=CUBESET("PowerPivot Data",Slicer_FiscalYear,"Set of Years</span></p>
<p class="equation"><strong>Equation 3</strong></p>
<p>which would look as shown in <strong>Figure 3</strong> when added to cell D1 to build our set of fiscal years.</p>
<p class="centre"><a href="http://msmvps.com/cfs-file.ashx/__key/CommunityServer.Blogs.Components.WeblogFiles/xldynamic/1512.Slicer-years-set-formula.png"><img border="0" src="http://msmvps.com/resized-image.ashx/__size/550x0/__key/CommunityServer.Blogs.Components.WeblogFiles/xldynamic/1512.Slicer-years-set-formula.png" alt="" /></a></p>
<p class="figure"><strong>Figure 3 – Slicer years set formula</strong></p>
<p>As can be seen, we use <strong>Slicer_FiscalYear</strong> as the <span class="functionsyntax"><i>set_expression</i></span>, so the set will include all selected values in that slicer, with the <span class="functionsyntax"><i>caption</i></span> signifying the cell contents.</p>
<p>So far, so good, but we still need to list those selected values. For this, we use the <strong>CUBERANKEDMEMBER</strong> function, which returns the nth, or ranked, member in a set. The syntax of this</p>
<p class="functionsyntax">=CUBERANKEDMEMBER(connection, set_expression, rank, [caption])</p>
<p>where <span class="functionsyntax"><i>connection</i></span> is the cube as before, <span class="functionsyntax"><i>set_expression</i></span> is the set of values to choose from, and <span class="functionsyntax"><i>rank</i></span> is nth value. So, to get the first member, we use</p>
<p class="function">=CUBERANKEDMEMBER("PowerPivot Data",$D$1,1)</p>
<p>for the second</p>
<p class="function">=CUBERANKEDMEMBER("PowerPivot Data",$D$1,2)</p>
<p>and so on.</p>
<p>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,</p>
<p class="function">=IFERROR(CUBERANKEDMEMBER("PowerPivot Data",$D$1,1),"")</p>
<p class="function">=IFERROR(CUBERANKEDMEMBER("PowerPivot Data",$D$1,2),"")</p>
<p>etc.</p>
<p><strong>Equation 4</strong></p>
<h2>Why Extra Ingredients?</h2>
<p>We could nest the <strong>CUBESET</strong> function within the <strong>CUBERANKEDMEMBER</strong>,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 <strong>CUBERANKEDMEMBER </strong>function, it is evaluated just the once. A small matter, but it makes the spreadsheet easier to maintain, and is more efficient.</p>
<h2>Cooked To Perfection</h2>
<p>It’s as simple as that.</p>
<p>But hang on a minute, have we overcooked it?</p>
<p>Looking at the syntax definition for these two functions, we can see that they both take <span class="functionsyntax"><i>set_expression</i></span> as an argument. The <strong>CUBESET</strong> function is passed the slicer values as its set, and in turned is passed to the <strong>CUBERANKEDMEMBER </strong>function as its set. </p>
<p>As the slicer values is a <span class="functionsyntax"><i>set_expression</i></span>, you would think that we should be able to pass the slicer values directly to <strong>CUBERANKEDMEMBER</strong> as a set and be done with. And so we can, these formulae</p>
<p class="function">=IFERROR(CUBERANKEDMEMBER("PowerPivot Data",Slicer_FiscalYear,1),"")</p>
<p class="function">=IFERROR(CUBERANKEDMEMBER("PowerPivot Data",Slicer_FiscalYear,2),"")</p>
<p>etc.</p>
<p class="equation"><strong>Equation 5</strong></p>
<p>work equally as well as those formulae in <strong>Equation 4</strong></p>
<h2>Managing The Ingredients</h2>
<p>The list of product categories can also be listed in a similar way using <strong>CUBESET</strong> and <strong>CUBERANKEDMEMBER</strong>. Here we do need <strong>CUBESET</strong> 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,</p>
<p class="function">=CUBESET("PowerPivot Data",<br />
"[DimProductCategory].[EnglishProductCategoryName].Children",<br />
"Set of Categories")</p>
<p class="equation"><strong>Equation 6</strong></p>
<p>As can be seen, <strong>.Children</strong> gets us all of the category values.</p>
<p>One thing to note is the use of the <span class="functionsyntax"><i>caption</i></span> argument. Again, this helps to highlight the cell containing the set.</p>
<p>We now have formulae that can define our full table, such as</p>
<p>D1: the formula in <strong>Equation 3</strong><br />
<span class="function">=CUBESET("PowerPivot Data",Slicer_FiscalYear,"Set of Years")</span></p>
<p>D2: the formula in <strong>Equation 6</strong><br />
<span class="function">=CUBESET("PowerPivot Data",<br />
"[DimProductCategory].[EnglishProductCategoryName].Children",<br />
"Set of Categories")</span></p>
<p>E5:I5: the formulae in <strong>Equation 5</strong><br />
<span class="function">=IFERROR(CUBERANKEDMEMBER("PowerPivot Data",Slicer_FiscalYear,1),"")<br />
=IFERROR(CUBERANKEDMEMBER("PowerPivot Data",Slicer_FiscalYear,2),"")</span><br />
etc.,</p>
<p>D6:D9: formulae for the product categories<br />
<span class="function">=IFERROR(CUBERANKEDMEMBER("PowerPivot Data",$D$2,1),"")<br />
=IFERROR(CUBERANKEDMEMBER("PowerPivot Data",$D$2,2),"")<br />
=IFERROR(CUBERANKEDMEMBER("PowerPivot Data",$D$2,1),"")<br />
=IFERROR(CUBERANKEDMEMBER("PowerPivot Data",$D$2,2),"")</span></p>
<p>And finally, in E6:I9, the formulae for the values<br />
<span class="function">=IFERROR(CUBEVALUE("PowerPivot Data","[Measures].[Sum of SalesAmount]",$D6,E$5),"")</span><br />
etc., each cell reflecting the correct product category ($D6) and fiscal year (E$5).</p>
<p>Our table now has a full set of values, and reflects the choices made in the fiscal year slicer.</p>
<p>(If we wished, we could add the product categories to a slicer, and make our table dynamically reflect that.)</p>
<h2>Ready to Serve?</h2>
<p>Although we have been diligent in storing the evaluated sets in one place rather than nest a <strong>CUBESET</strong> function within the <strong>CUBERANKEDMEMBER</strong> function, but there are still a number of things going on here that I just don’t like:</p>
<ul>
<li>The connection is hard-coded, multiple times</li>
<li>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</li>
<li>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 <strong>Figure 4</strong>. This may be what is required in some instances, in others we may want to show each year’s values individually.</li>
</ul>
<p class="centre"><a href="http://msmvps.com/cfs-file.ashx/__key/CommunityServer.Blogs.Components.WeblogFiles/xldynamic/3666.Showing-all-years-as-a-total-_2E00__2E00__2E00_.png"><img border="0" src="http://msmvps.com/resized-image.ashx/__size/550x0/__key/CommunityServer.Blogs.Components.WeblogFiles/xldynamic/3666.Showing-all-years-as-a-total-_2E00__2E00__2E00_.png" alt="" /></a></p>
<p class="figure"><strong>Figure 4 - Showing all years as a total with no slicer selections</strong></p>
<p>These ‘difficulties’ can be overcome relatively easily.</p>
<p>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 <strong>_cube</strong>, 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).</p>
<p>The rank is managed by using a function that returns a variable number depending upon the row or column of the cell, namely <span class="functionsyntax"><i>ROW([reference])</i></span> or <span class="functionsyntax"><i>COLUMN([reference])</i></span>. You might think that you can use <strong>COLUMN(A1)</strong> in E5, and copy that across so that it updates to <strong> COLUMN(B1)</strong>,<strong> COLUMN(C1)</strong>, 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 <strong>COLUMN(A1)</strong> will update to <strong>COLUMN(B1)</strong> 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.</p>
<p>The suggested change is to use <strong>COLUMN()-COLUMN($D$5)</strong>, 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 <strong>COLUMN()-COLUMN($E$5)</strong>, which means the formula that was in cell E5 which has now moved to cell F5 still return 1 for that calculation.</p>
<p>Similarly, the category list will use <strong> ROW()-ROW($D$5)</strong>.</p>
<p>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</p>
<p class="function">=CUBESET(_cube,Slicer_FiscalYear,"Slicer Years")</p>
<p>As we showed before, we can get a set of all year regardless of slicer selection with the <strong>CUBESET</strong> function and the member’s children property, as in</p>
<p class="function">CUBESET(_cube,"[DimDate].[FiscalYear].Children","Dimension Years")</p>
<p>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</p>
<p class="function">IF(CUBERANKEDMEMBER(_cube,Slicer_FiscalYear,1)<>"All", …</p>
<p>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</p>
<p class="function">=IF(CUBERANKEDMEMBER(_cube,Slicer_FiscalYear,1)<>"All",<br />
CUBESET(_cube,Slicer_FiscalYear,"Slicer Years"),<br />
CUBESET(_cube,"[DimDate].[FiscalYear].Children","Dimension Years"))</p>
<p>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 <strong>Figure 5.</strong></p>
<p class="centre"><a href="http://msmvps.com/cfs-file.ashx/__key/CommunityServer.Blogs.Components.WeblogFiles/xldynamic/2555.Showing-all-years-with-no-slicer-_2E00__2E00__2E00_.png"><img border="0" src="http://msmvps.com/resized-image.ashx/__size/550x0/__key/CommunityServer.Blogs.Components.WeblogFiles/xldynamic/2555.Showing-all-years-with-no-slicer-_2E00__2E00__2E00_.png" alt="" /></a></p>
<p class="figure"><strong>Figure 5 - Showing all years with no slicer selections</strong></p>
<h2>What's For Dessert?</h2>
<p>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.</p><div style="clear:both;"></div><img src="http://msmvps.com/aggbug.aspx?PostID=1821046" width="1" height="1">Excel 2007cubeOLAPExcel 2010formulasPowerPivotdynamicSlicerData Analysis