Using MDX to Calculate Both Values and Percentages for Analysis Services

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


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.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Wednesday, March 2, 2016 - 2:42:05 AM - Venkat Back To Top (40835)

 

Very good post,

Wednesday, July 1, 2015 - 2:09:26 AM - Maria Luz Munoz Back To Top (38089)

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 (27248)

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 (21511)

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 (21473)

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 (21433)

"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 9, 2013 - 6:02:20 PM - Paul te Braak Back To Top (21355)

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 8, 2013 - 7:17:00 PM - Dallas Snider Back To Top (21326)

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 8, 2013 - 10:43:41 AM - Joel Mamedov Back To Top (21318)

 

Thank you for the article.

How about handling division by zero?

And formatting percentage that will look like percentage.

 














get free sql tips
agree to terms