Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using MDX to Calculate Both Values and Percentages for Analysis Services


By:   |   Read Comments (9)   |   Related Tips: > Analysis Services Measure Groups

Attend these FREE MSSQLTips webcasts >> click to register


Problem

When creating pie charts using data from Analysis Services, having the MDX query calculate and return the percentages along with the counts or sums is extremely efficient. In this tip, we walk through an example of how this can be done.

Solution

The solution presented in this tutorial will utilize a calculated member using the WITH keyword to perform the percentage calculation. In the code sample provided below, we will get the percentage distribution and sum of the measure Internet Sales-Sales Amount sliced by the Gender attribute of the Customer dimension. The following screenshot shows the aforementioned measure and dimensional attributes as they appear in Query Designer.

creating pie charts using data from Analysis Services

In the following code sample we define the name of our calculated member to be [Measures].[Percentage] in line 2. Next in lines 3 and 4 we define the MDX division operation to calculate the percentage. In line 5, the number format is defined to return 4 digits to the right of the decimal point. In line 8, we select the calculated member measure [Measures].[Percentage] and the existing measure [Measures].[Internet Sales Amount]. Line 9 of the sample code selects all of the values in the Customer dimension's Gender attribute.

WITH 
   MEMBER [Measures].[Percentage] AS
   [Measures].[Internet Sales Amount]/
   ([Customer].[Gender].currentmember.parent,[Measures].[Internet Sales Amount]), 
   FORMAT_STRING = '0.0000'
SELECT 
NON EMPTY {[Measures].[Percentage], [Measures].[Internet Sales Amount] } ON COLUMNS, 
NON EMPTY {([Customer].[Gender].[Gender].ALLMEMBERS ) } ON ROWS 
FROM [Adventure Works] 

Please note that it might be intuitive to want to switch lines 3 and 4 so the seemingly higher value (the total sales) is in the denominator after the forward slash (/) and the smaller value (the amount per gender) is in the numerator before the forward slash.

The results from the sample query above as they appear in SQL Server Management Studio are shown below. This result set allows for the use of one query in Reporting Services to provide the percentages needed for a pie chart and the actual values that could be placed in a corresponding table.

as they appear in SQL Server Management Studio
Next Steps
  • After crafting and testing the MDX query in SQL Server Management Studio, copy and paste the MDX query into the Query Designer text editor window in Visual Studio or Report Builder while designing the report.
  • Please refer to the following tip for further assistance with calculated members in MDX: Building Calculated Members for a SSRS Report using MDX.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Dallas Snider Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, March 02, 2016 - 2:42:05 AM - Venkat Back To Top

 

Very good post,


Wednesday, July 01, 2015 - 2:09:26 AM - Maria Luz Munoz Back To Top

Hi Dr. Dallas,

In generating the Gross margin percentage, I get the calculation correct.  the code is

CREATE MEMBER CURRENTCUBE.[GL Codes].[PnL Codes - H].[All].[Gross Margin %]
 AS case when [GL Codes].[PnL Codes - H].[MGTREPGRP1L2].&[1000] = 0 and  [GL Codes].[PnL Codes - H].[MGTREPGRP1L2].&[1100] > 0 then 1.0  when [GL Codes].[PnL Codes - H].[MGTREPGRP1L2].&[1000] = 0  and  [GL Codes].[PnL Codes - H].[MGTREPGRP1L2].&[1100] < 0  then -1.0  when [GL Codes].[PnL Codes - H].[MGTREPGRP1L2].&[1000] = 0  and  [GL Codes].[PnL Codes - H].[MGTREPGRP1L2].&[1100] = 0  then 0.0 else [GL Codes].[PnL Codes - H].[All].[Gross Margin]/[GL Codes].[PnL Codes - H].[MGTREPGRP1L2].&[1000] end,
FORMAT_STRING = "0.0000", VISIBLE = 1

Then I want to calculate the YTD with the next code

CREATE MEMBER CURRENTCUBE.[Financial Period].[YYHHQQMMWK - H].[All].[FinancialYear YTD]
 AS SUM ( PERIODSTODATE ( [Financial Period].[YYHHQQMMWK - H].[Year] , StrToMember (" [Financial Period].[YYHHQQMMWK - H].[Year].&["+Format(now(),"yyyy")+"] ") ) ),
FORMAT_STRING = "$#,##0;($#,##0)", VISIBLE = 1

 

The browser shows, the gross margin for the YTD as $.   I guess the issue comes from the hierarchies used for the calculations, but I'm lost in finding the best way.

             Year    YTD

Total revenue 100    100

Total costs    50     50

Gross Margin   50     50

Gross Margin   50%    $0

 


Wednesday, October 23, 2013 - 5:20:55 PM - Andrew DeCarlo Back To Top

Hey there, thanks a bunch for this post. This post almost answers the question I am looking to answer. I am trying to find percentages within a dimension. For example, I see we have this [Customer].[Gender] dimension with the values of male and female. Do you know of anyways that you can create a percentage for the different values, the percentage of Female customers and the percentage of Male customers? Thanks!


Thursday, January 17, 2013 - 8:04:42 AM - Dallas Snider Back To Top

The code is efficient in terms of how much work is involved to create a report.  Instead of calculating the percentages in the report (which can be tricky to a beginner in SQL Server Reporting Services), the query has calculated the percentages for you.  


Tuesday, January 15, 2013 - 11:35:32 PM - Phil T Back To Top

Intersting post - many thanks.

 

... but the original thrust of this is that it is that "return the percentages along with the counts or sums is extremely efficient", please can you explain what characteristics make this so efficient?

P.S. Davos has a very valid point, thanks for your comment too.


Monday, January 14, 2013 - 1:29:29 AM - Davos Back To Top

"Please note that it might be intuitive to want to switch lines 3 and 4 so the seemingly higher value (the total sales) is in the denominator after the forward slash (/) and the smaller value (the amount per gender) is in the numerator before the forward slash."

 

That may be true but it might help novice users if you explain to them why their intuition would be wrong in that case. I think it's difficult for MDX novices to understand the way currentmember works in calculated members, and also that the ".parent " part of the expression means that the denominator as you've presented it is not in fact the smaller of the two values.

 

You can also just use this syntax which will default to 2 decimal places. It's very rare that you would want more than 2:

FORMAT_STRING + "PERCENT"


Wednesday, January 09, 2013 - 6:02:20 PM - Paul te Braak Back To Top

For div by zero errors you should check the denominator for a zero (or null value)

WITH MEMBER [Measures].[Percentage] AS

iif( ([Customer].[Gender].currentmember.parent,[Measures].[Internet Sales Amount]) = 0

   , 0

   , [Measures].[Internet Sales Amount]/ ([Customer].[Gender].currentmember.parent,[Measures].[Internet Sales Amount])

) , FORMAT_STRING = '0.0%'

SELECT {[Measures].[Percentage], [Measures].[Internet Sales Amount] } ON COLUMNS

, {([Customer].[Gender].[Gender].ALLMEMBERS ) } ON ROWS

FROM [Adventure Works]

I also not that the NON EMPTY inclusion on columns is irrelevant


Tuesday, January 08, 2013 - 7:17:00 PM - Dallas Snider Back To Top

To format the results to show a percentage, replace the format string above with the following string: 

FORMAT_STRING = '0.0000%'

My original intent was to use the cell formatting in SSRS to format the number to be a percentage. 


Tuesday, January 08, 2013 - 10:43:41 AM - Joel Mamedov Back To Top

 

Thank you for the article.

How about handling division by zero?

And formatting percentage that will look like percentage.

 


Learn more about SQL Server tools