1<!DOCTYPE html> 2<html lang="en-US"> 3 4<head>
5<script>window.msAdsQueue = window.msAdsQueue || [];</script>
5 6
6<script async src="https://adsdk.microsoft.com/pubcenter/sdk.js?siteId=10321950&publisherId=253231567" crossorigin="anonymous"></script>
6 7 8 <meta charset='utf-8'> 9 <meta http-equiv="X-UA-Compatible" content="chrome=1"> 10 <meta name="viewport" content="width=device-width,maximum-scale=2"> 11 <meta name="description" content="OLAP PivotTable Extensions : An Excel add-in which extends the functionality of PivotTables on Analysis Services cubes, Excel Data Model (Power Pivot), and Power BI"> 12 13 <link rel="stylesheet" type="text/css" media="screen" href="/assets/css/style.css?v=073d1862b94e601ba17e66c2a934a282d653f47e"> 14 15 <link title="OLAP PivotTable Extensions Releases" href="https://github.com/OlapPivotTableExtensions/OlapPivotTableExtensions/releases.atom" rel="alternate" type="application/atom+xml"> 16 <link title="OLAP PivotTable Extensions Source Code Commits" href="https://github.com/OlapPivotTableExtensions/OlapPivotTableExtensions/commits/master.atom" rel="alternate" type="application/atom+xml"> 17 18 <link rel="shortcut icon" type="image/x-icon" href="https://olappivottableextensions.github.io/favicon.ico" /> 19 20 <!-- Begin Jekyll SEO tag v2.8.0 --> 21<title>Calculations Help | OLAP PivotTable Extensions</title> 22<meta name="generator" content="Jekyll v3.9.3" /> 23<meta property="og:title" content="Calculations Help" /> 24<meta property="og:locale" content="en_US" /> 25<meta name="description" content="Resources and examples of MDX calculations that can be used in OLAP PivotTable Extensions" /> 26<meta property="og:description" content="Resources and examples of MDX calculations that can be used in OLAP PivotTable Extensions" /> 27<link rel="canonical" href="https://olappivottableextensions.github.io/Calculations-Help.html" /> 28<meta property="og:url" content="https://olappivottableextensions.github.io/Calculations-Help.html" /> 29<meta property="og:site_name" content="OLAP PivotTable Extensions" /> 30<meta property="og:type" content="website" /> 31<meta name="twitter:card" content="summary" /> 32<meta property="twitter:title" content="Calculations Help" />
33<script type="application/ld+json"> 34{"@context":"https://schema.org","@type":"WebPage","description":"Resources and examples of MDX calculations that can be used in OLAP PivotTable Extensions","headline":"Calculations Help","url":"https://olappivottableextensions.github.io/Calculations-Help.html"}</script>
34 35<!-- End Jekyll SEO tag --> 36 37 38 <style> 39 .button { 40 width: 80px; 41 margin: 0px 2px; 42 padding: 5px 10px; 43 height: 35px; 44 background: #bbbbbb; 45 color: black; 46 font-weight: bold; 47 } 48 49 .button:hover { 50 background: #0090ff; 51 color: white; 52 } 53 54 .button:hover a { 55 color: white; 56 } 57 58 .button a { 59 text-align: center; 60 vertical-align: middle; 61 margin: auto; 62 } 63 64 .button a:hover { 65 color: white; 66 } 67 68 </style> 69 70</head> 71 72<body> 73 74 <!-- HEADER --> 75 <div id="header_wrap" class="outer" style="padding-top:10px;padding-bottom:0px;margin-top:0px;margin-bottom:0px"> 76 <header class="inner" style="padding-top:10px;padding-bottom:0px;margin-top:0px;margin-bottom:0px"> 77 78 79 <table style="border-style:none; border-bottom-style:none; border-width:0px; padding:0px;margin-top:0px;margin-bottom:0px"> 80 <tr> 81 <td style="border-style:none; border-width:0px;padding-top:0px;padding-bottom:0px" colspan="2"> 82 <h1 id="project_title">OLAP PivotTable Extensions</h1> 83 <h2 id="project_tagline">An Excel add-in which extends the functionality of PivotTables on Analysis Services cubes, Excel Data Model (Power Pivot), and Power BI</h2> 84 </td> 85 </tr> 86 <tr> 87 <td style="border-style:none; border-width:0px;padding-top:0px;padding-bottom:0px;margin-top:0px;margin-bottom:0px;white-space:nowrap;"> 88 <section id="menu"> 89 <a href="https://olappivottableextensions.github.io"><span class="button">Home</span></a> 90 <a href="https://github.com/OlapPivotTableExtensions/OlapPivotTableExtensions/releases"><span class="button">Releases</span></a> 91 <a href="https://github.com/OlapPivotTableExtensions/OlapPivotTableExtensions/issues"><span class="button">Issues</span></a> 92 </section> 93 </td> 94 <td width=96 style="vertical-align:bottom; border-style:none; border-width:0px;padding-top:0px;padding-bottom:0px;margin-top:0px;margin-bottom:0px"> 95 <SECTION> 96 <A href="https://github.com/OlapPivotTableExtensions/OlapPivotTableExtensions/releases/download/v0.9.7/OLAP.PivotTable.Extensions.Setup.v0.9.7.exe" title="Download the installer"><img src="images/download.png" alt="Download the installer" style="border: 0; box-shadow:none;padding-top:0px;padding-bottom:0px;margin-top:0px;margin-bottom:0px;width:96px;height:auto" /></A> 97 </SECTION> 98 </td> 99 </tr> 100 </table> 101 102 </header> 103 </div> 104 105 106 107 108 109 <!-- MAIN CONTENT --> 110 <div id="main_content_wrap" class="outer" style="padding-top:10px;padding-bottom:10px"> 111 <section id="main_content" class="inner" style="padding-top:10px;padding-bottom:10px"> 112 <h3 id="calculations-help">Calculations Help</h3> 113 114<p>This page is a resource for the OLAP PivotTable Extensions if you are creating private PivotTable calculations and need help. The formula used is a standard Analysis Services MDX expression. All formulas are executed on the Analysis Services server.</p> 115 116<p>The best place to ask for help with MDX expressions is at the <a href="http://stackoverflow.com/questions/tagged/mdx">MDX tag on StackOverflow</a> or the <a href="http://social.msdn.microsoft.com/forums/en-US/sqlanalysisservices/threads/">Microsoft Analysis Services Forums</a>.</p> 117 118<h4 id="example-calculations">Example Calculations</h4> 119<p>The following calculations work against Microsoftâs sample Adventure Works cube:</p> 120 121<h5 id="ratio">Ratio</h5> 122<pre><code class="language-mdx">IIf( 123 [Measures].[Internet Order Count] = 0 124 ,null 125 ,[Measures].[Internet Tax Amount] / [Measures].[Internet Order Count] 126) 127</code></pre> 128 129<h4 id="mdx-references">MDX References</h4> 130 131<ul> 132 <li>
132<a href="http://msdn2.microsoft.com/en-us/library/ms145970.aspx">MDX Function Reference</a></li> 133 <li><a href="http://www.mdxpert.com">MDX Function Usage Examples</a></li> 134 <li><a href="https://docs.microsoft.com/en-us/sql/mdx/vba-functions-in-mdx-and-dax">VBA functions</a> you can use in MDX expressions.</li> 135 <li>If Excel 2003 or later is installed on the Analysis Services server, these <a href="http://web.archive.org/web/20090722011824/http://msdn.microsoft.com/en-us/library/aa178231(SQL.80).aspx">Excel functions</a> can be used in your MDX expressions. Note that is old Analysis Services 2000 documentation but still applies to newer Analysis Services Multidimensional models. However, documentation is not clear on exactly <a href="https://connect.microsoft.com/SQLServer/feedback/details/124864/shouldnt-need-to-separately-install-excel-to-get-excel-functions-in-mdx">what version of Excel to install on the server</a>, so your mileage may vary.</li> 136</ul> 137 138<h4 id="advanced">Advanced</h4> 139 140<p>The following are advanced tricks that may require more advanced knowledge of Analysis Services, your cube, and MDX.</p> 141 142<h4 id="calculated-members-on-dimensions">Calculated Members on Dimensions</h4> 143 144<p>If you type in simply âMy Calculationâ or <code class="language-plaintext highlighter-rouge">[Measures].[My Calculation]</code> into the Calculation Name field in Calculations tab of OLAP PivotTable Extensions, you are creating a calculated measure. Calculations can also be placed on dimensions. This is appropriate when the calculation applies across to all measures.</p> 145 146<p>As an example, you could define the following calculation:</p> 147<ul> 148 <li>Calculation Name: <code class="language-plaintext highlighter-rouge">[Product].[Product Categories].[Bikes].[Road Bikes + 25%]</code></li> 149 <li>Formula: <code class="language-plaintext highlighter-rouge">[Product].[Product Categories].[Bikes].[Road Bikes] * 1.25</code></li> 150</ul> 151 152<p><img src="Calculations%20Help_DimensionCalcMemberFormula.png" alt="" /></p> 153 154<p>These dimension calculated members then show up in the PivotTable just like normal dimension members:</p> 155 156<p><img src="Calculations%20Help_DimensionCalcMemberPivotTable.png" alt="" /></p> 157 158<h4 id="putting-calculations-in-folders-as2008-only">Putting Calculations in Folders (AS2008 only)</h4> 159 160<p>When you build a private calculation in a PivotTable, it goes in the Values folder at the top of the field list. If your server is Analysis Services 2008, you can put that measure into a subfolder in the Values folder by using the DISPLAY_FOLDER property:</p> 161 162<div class="language-plaintext highlighter-rouge"><div class="highlight"><pre class="highlight"><code>[Measures].[Internet Sales Amount] * 2 163,DISPLAY_FOLDER = 'My Folder' 164</code></pre></div></div> 165 166<p>Or you could put it in the folder for a measure group:</p> 167 168<div class="language-plaintext highlighter-rouge"><div class="highlight"><pre class="highlight"><code>[Measures].[Internet Sales Amount] * 2 169,ASSOCIATED_MEASURE_GROUP = 'Internet Sales' 170</code></pre></div></div> 171 172<p>Or you could put it in a subfolder inside a particular measure group:</p> 173 174<div class="language-plaintext highlighter-rouge"><div class="highlight"><pre class="highlight"><code>[Measures].[Internet Sales Amount] * 2 175,DISPLAY_FOLDER = 'My Folder' 176,ASSOCIATED_MEASURE_GROUP = 'Internet Sales' 177</code></pre></div></div> 178 179<h4 id="as2000-syntax">AS2000 Syntax</h4> 180 181<p>For PivotTables running off an Analysis Services 2000 cube, note that the syntax for calculated members in AS2000 was less flexible. You must surround your entire MDX with single quotes like:</p> 182 183<div class="language-plaintext highlighter-rouge"><div class="highlight"><pre class="highlight"><code>'[Measures].[Internet Tax Amount] / [Measures].[Internet Order Count]' 184</code></pre></div></div> 185 186<h4 id="powerpivot">PowerPivot</h4> 187 188<p>For PowerPivot, here is the official policy. If itâs not done using the Excel UI, itâs not supported by Microsoft. So using OLAP PivotTable Extensions (which uses the Excel API) wonât be supported by Microsoft. So use OLAP PivotTable Extensions on top of PowerPivot PivotTables at your own risk.</p> 189 190<p>That being said, using OLAP PivotTable Extensions to add calculations to a PowerPivot PivotTable works fine most of the time. It lets you do things which are not possible with PowerPivot itself, for example, adding calculated members to dimensions (so that the calculation applys to every measure).</p> 191 192<p>Note the Solve_Order workaround mentioned in the following <a href="https://olappivottableextend.codeplex.com/discussions/219957">discussion</a>. Also note the tips on troubleshooting DAX errors in PowerPivot mentioned in that discussion. Finally, note that the calculations you can add in OLAP PivotTable Extensions are MDX calculations, not DAX calculations.</p> 193 194<p>Unfortunately, PowerPivot for Excel 2013, which comes installed with Excel, but has to be activated under COM Add-ins, has removed the ability add MDX calculations to PivotTables. For more about Excel 2013 and the Excel Data Model, see the <a href="/Excel-2013.html">Excel 2013</a> page.</p> 195 196 </section>
197 <section id="ad_content" class="inner" style="padding-top:10px;padding-bottom:10px"> 198 <div style="color: gray;font-size: smaller; font-style: italic;">Advertisement:</div> 199 200 <div id="ms-ad-128075890"></div> 201
201<script> 202 window.msAdsQueue.push(() => { 203 window.pubCenterSdk.render({ 204 adUnitId: "128075890", 205 elementId: "ms-ad-128075890" 206 }); 207 }); 208 </script>
208 209 210 <div data-type="ad" data-publisher="olappivottableextensions.github.io" data-format="300x250" data-zone="bottom" data-tags="excel%2canalysis%20services%2cssas%2colap%2cpivottables%2cpowerpivot%2cadd-in%2ccube%2cmdx%2cplugin%2csearch%20field%20list%2ctabular%2cexcel%20services%2csearch%20cube%2csearch%20dimensions"></div> 211 </section> 212 </div> 213 214 <!-- FOOTER --> 215 <div id="footer_wrap" class="outer"> 216 <footer class="inner"> 217 <a id="forkme_banner" href="https://github.com/OlapPivotTableExtensions/OlapPivotTableExtensions">View Source Code on GitHub</a> 218 </footer> 219 </div> 220 221 222 223
223<script type='text/javascript'> 224 function _dmBootstrap(file) { 225 var _dma = document.createElement('script'); 226 _dma.type = 'text/javascript'; 227 _dma.async = true; 228 _dma.src = ('https:' == document.location.protocol ? 'https://' : 'http://') + file; 229 (document.getElementsByTagName('head')[0] || document.getElementsByTagName('body')[0]).appendChild(_dma); 230 } 231 function _dmFollowup(file) { if (typeof DMAds === 'undefined') _dmBootstrap('cdn2.DeveloperMedia.com/a.min.js'); } 232 (function () { _dmBootstrap('cdn1.DeveloperMedia.com/a.min.js'); setTimeout(_dmFollowup, 2000); })(); 233 </script>
233 234</body> 235</html> 236
Line numbers count LF bytes from the start of the resource, as the search results do. Vendor segments are library code the classifier recognised; they are stored but not indexed. Bytes are shown as Latin1 characters, one per byte.