Median calculated in MDX SSAS

0
votes

here is my case. I have small training in creating OLAP cube in SSAS and as part of it I need to calculate median time from creation issue to resolve issue. So according to microsoft docs I should use MEDIAN function in MDX. So here is my code:

MEDIAN([Issue].[Issue ID],[Measures].[Hours Resolved])

Short explanation: [Measures].[Hours Resolved] it's a measure calculated in database from dimensions "resolved issue time" - "creation issue time" with DATEDIFF function. Both are smalldatetime datatype.

And it looks like it works in proper way for case on the screen below. Exept "Grand Total" value in Mediana column.

enter image description here

I believe that Grand Total value should be 12 becasue this is proper score according to way the median should be calculated (checked also in Excel). So am I wrong here and this is proper behaviour? Or maybe I miss something in my calculation or configuration in SSAS?

Second case in this exercise. When I will add for example Group Name column like on the picture below:

enter image description here

In my understanding value mediana column for let's say CRM part should be 9.

Can you please guide me if I'm right or wrong? If I'm right how to achieve this. Or if I'm wrong please point mistake in my solution. This is my 1st time when I'm calculating median.

1
ssasmdx

1 Answers

1
votes

It's a little embrassing that no one even look at it - 16 views and probably all mine - well doesn't matter anymore because I figured it out myself. For proper median calculation for all dimensions I should use Median function and Scope function in MDX. So here is the code if someone will face the same problem in future:

CREATE MEMBER CURRENTCUBE.[Measures].[Median]

AS Null

VISIBLE = 1;

SCOPE([Measures].[Median]);

THIS = MEDIAN([View Issue Median].[Issue ID].[Issue ID], [Measures].[Hours Resolved]);

END SCOPE;