# Using Statistical Functions to build Calculated Members

• Ok I have a measure defined in my cube.

It's a sum measure on a value from the table called loadtime.

The measure is [measures].[sum of loadtime]

I want to calculate the Median value of this across dimensions.

I've got a time dimension call Time.

So I created a Calculation called [Median Loadtime]

I've tried the following for the Expression value.

The first one doesn't seem to work (no errors, just no values come back when I try to manipulate the cube in excel. The second doesn't calculate the median, it seems to sum up the values (which I think was because I was using a sum measure as my base).

Ok so I went back to the Cube Structure and defined a new measure off of Loadtime that had no aggregations. I called this Loadtime

I altered my attempts above to use this new measure, neither returns any results. The cube builds fine, deploys with no issue, but it won't give me a median.

I've been spending the better part of the day looking for a step by step tutorial of just what the heck I need to do to use the built in statistical functions to create a calculation that will give me the median.

I understand that I will have to pick a dimensional set to make this work, but I thought using my time dimension and encompassing all of it would work.

This shouldn't be this difficult, but I fear my knowledge of SQL is getting in my way of understanding of MDX and cubes.

Does anybody have any tips? This problem keeps coming back to haunt me when somebody noticesthat the median measure of my cubes are incorrect. Everything I've found seems to say that the right way to do this is what I've done.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

• Ok after analyzing a sample cube, I figured out that what I wanted, which is a Median measure of the values underlying the base cells is basically not doable with the median formula, I instead tried to mimick the median formulas I had seen in the sample.

So I adjusted my medians to be

CREATE MEMBER CURRENTCUBE.[Enterprises and Users].[Users].[All].[Median of Enterprise Users]

AS MEDIAN([Enterprises and Users].[Users].[All].Children ),

VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[Agencies].[Agency List].[All].[Median of Agencies]

AS MEDIAN([Agencies].[Agency List].[All].Children ),

VISIBLE = 1;

I would expect that like in a sample cube, I should be able to take either the [Enterprises and Users].[Users] dimension or the [Agencies].[Agency List] dimension and put them on the rows, then drag a normal measure (count of screen uses for instance) to the data cell, and I should see a median value at the bottom of the rows.

Instead, the returned data set seems to leave out the median calculation entirely.

[Measures].[Count of Uses] is defined as a count measure on the Cube Structure page.

Is there still a trick I need to use to get the Median values in there?

My calulcations are as such (if that helps).

CALCULATE;

CREATE MEMBER CURRENTCUBE.[MEASURES].[Ratio of Screens used to number of users actually using the system]

AS [Measures].[Count of Uses]/[Measures].[Distinct Actual User],

FORMAT_STRING = "#,#.00",

NON_EMPTY_BEHAVIOR = { [Distinct Actual User] },

VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].[Ratio of Distinct Screens used to number of distinct users of the system]

AS [Measures].[Unique Screens Viewed]/[Measures].[Distinct Actual User],

FORMAT_STRING = "#,#.00",

NON_EMPTY_BEHAVIOR = { [Distinct Actual User] },

VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].[Average Loadtime discounting Zero Values]

AS (IIF(ISEMPTY([Measures].[Count of Uses with Non Zero Access times]),null,IIF([Measures].[Count of Uses with Non Zero Access times]=0,0,[Measures].[Sum of Loadtime]/[Measures].[Count of Uses with Non Zero Access times]))),

FORMAT_STRING = "#.00",

VISIBLE = 1;

AS (IIF(ISEMPTY([Measures].[Count of Uses]),null,IIF([Measures].[Count of Uses]=0,0,[Measures].[Sum of Loadtime]/[Measures].[Count of Uses]))),

FORMAT_STRING = "#.00",

NON_EMPTY_BEHAVIOR = { [Count of Uses] },

VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].[Screen Uses per User]

AS [Measures].[Count of Uses]/[Measures].[User Count],

FORMAT_STRING = "#,#.00",

NON_EMPTY_BEHAVIOR = { [User Count] },

VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].[Unique Screen Uses Per User]

AS [Measures].[Unique Screens Viewed]/[Measures].[User Count],

FORMAT_STRING = "#,#.00",

NON_EMPTY_BEHAVIOR = { [User Count] },

VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[Enterprises and Users].[Users].[All].[Median of Enterprise Users]

AS MEDIAN([Enterprises and Users].[Users].[All].Children ),

VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[Agencies].[Agency List].[All].[Median of Agencies]

AS MEDIAN([Agencies].[Agency List].[All].Children ),

VISIBLE = 1;

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

• Mark,

As far as I can tell, your last attempt should give you the result you want. As far as making the result visible, try using the ADDCALCULATEDMEMBERS function on the (row?) specifier.

e.g.

WITH MEMBER [GL Accounts].[GL Account].Mark AS MEDIAN([GL Accounts].[GL Account].[All].CHILDREN), VISIBLE = 1

SELECT

[Measures].[GL Amount] ON 0

FROM

[AcornEPS]

Using SSMS and Excel I confirmed that the result returned is the accurate median.

HTH,

Steve.

• Thanks Steve,

I'm glad to know I at aleast got the formula right for the calculation.

I'm not writing MDX, just using Excel or the built in browser to look at the cube.

I used the same Median formula on the Sample cube I picked up for Northwind from the SQL Server 2005 Analysis Services Step by Step book, and it works in that cube.

I don't get erorr messages when I build, deploy, or process the cube, but the calculated values that should be at the bottom of the listed out dimension do not show up in the dimensions when dragged into the workspace, or used within Excel as a client to SSAS.

i.e. I drag [Enterprises and Users].[Users] dimension to the row workspace. From my understanding the [Median of Enterprise Users] median value of the other values at that level should show up towards the bottom of the viewed cube surface.

How would I specify ADDCALCULATEDMEMBERS to the scripted Calculations on the third tab of the cube designer?

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

• I think it depends on what version of excle you're running but on my 2010, if you right click the pivottable and select pivottable options, on the display tab you can select/deselect the option for showing calc members from the olap server.

hth,

Steve.

• But the built in browser for BIDS should show it by default right?

Because it doesn't show it either. :/

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

• I take it back... It's there now (not sure why), and indeed if I go to pivot table options I can make it show up. 🙂

Awesomeness. At least it works now.

So to make sure I understand.

The statistical functions (STDDEV, MEDIAN, etc) only work when displayed as an extra dimensional value on the dimensions, there isn't a way to make it show up as a measure.

Correct? If so, at least I get it, it sucks because I really need a Median measure for the underlying values, but at least it makes sense.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

• You should be able to make a Median measure, the only suckful thing is that you need to i) tie it to a set (e.g. 'Enterprise and Users') and ii) tie it to measure (otherwise you get infinite recursion).

Steve.

• I did that like this.

CREATE MEMBER CURRENTCUBE.[Measures.[Median of Enterprise Users]

AS MEDIAN([Enterprises and Users].[Users].[All].Children,[Measures].[Average Loadtime] ),

VISIBLE = 1;

I found when brought onto the data space, it would sum the loadtimes, not find the median.

So I adjusted the cube by creating a no-aggregate measure of Loadtime called Loadtime and changed the calculation to what I have below.

CREATE MEMBER CURRENTCUBE.[Measures.[Median of Enterprise Users]

VISIBLE = 1;

Once I did this, the Median measure would not return a value at all.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

• Not sure what to tell you Mark. I couldn't try the first approach as my sample cube didn't have a time dim, so no average over time. I added a measure in (what i think is exactly) the same way as you did, and it returns data nicely.

CREATE MEMBER CURRENTCUBE.[Measures].Mark AS

MEDIAN([GL Accounts].[GL Account].MEMBERS, [Measures].[GL Amount]), VISIBLE = 1;

I guess one difference is that i used 'members' versus the children of the All member.

Steve.

• Oh it's not an average over time.

It's Two dimensions being used to calculate the average/Mean for each cell.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

• I have done it this way. It is working perfect

CREATE MEMBER CURRENTCUBE.[Measures].Electivemedianlos_Test

AS Median

(

{[Date].[Calendar].CurrentMember.Children},

[Measures].[LOS]

),

FORMAT_STRING = "#,##0.00;-#,##0.00"

Regards,

Ranjith

Ranjith Lekamalage
MCITP SQL 2012 (BI Development)

• Can you try this

CREATE MEMBER CURRENTCUBE.[Measures.[Median of Enterprise Users]

AS MEDIAN(

{[Enterprises and Users].[Users].[All].Children}