Calculated member: sum / count

Question

Hi

I think this is a rather simple question, but I can't seem to make it work. Basically I am trying to create an calculated member returning an average of certain measures. Using avg() does not return the correct values. Thus I'm trying to build a custom average, but I can't figure out how to count measures on dimension members.

We have a setup where the measures are all calculated (MeasureNo). The intented average will need to be calculated from only closed cases (defined by another dimension). The measure used is response time as integer which needs to be filtered on the two dimensions. 

Pseudo formula:

"sum of" ([CaseStatus A],[MeasureNo 1100],[Measures].[CaseResponseTime]) / "count of" ([CaseStatus A],[MeasureNo 1100],[Measures].[CaseResponseTime])

Answer 1

Hi Reloath,

1. For Sum :

WITH

MEMEBR [Measures].[TheSum] AS

(

[CaseStatus A],

[MeasureNo 1100],

[Measures].[CaseResponseTime]

)

2. Count

Say "measure group" of [Measures].[CaseResponseTime]is "Measure Group 1"

Create a separate measures for distinct count

Steps :

A. Open the cube in BIDS

B. Right click on measure group "Measure Group 1" and select "new measure"

C. Select usage as "Count of rows"

D. Click "OK"

This will create a "measure group" and  measure which is count of rows.

Rename the newly create measure group as  "Measure Group 1" and measure as "MyCount"

E. Now create the member for count

MEMEBR [Measures].[TheCount] AS

(

[CaseStatus A],

[MeasureNo 1100],

[Measures].[MyCount]

)

F. Now your average will be

MEMEBR [Measures].[TheAverage] AS

[Measures].[TheSum]/[Measures].[TheCount]

Hope this helps,

Ashim

---

Note : If this is helpful, Do not forget to mark as "Answered" or "Helpful"

Answer 2

Hi Ashim

Thank you very much for a very well documented solution. This will definately work. However I do wonder if it is possible to solve the "sum issue" with MDX - i.e a way to count all the measures with [CaseStatus A] & [MeasureNo 1100]? Somehow it seems like a lot of extra data to build a new measure group only to count the measures within.

Answer 3

Marked your post as answer as it solves the issue. Thanks.

Yet, I am curious if it is possible to count the touples using MDX? If so, would it be a better solution than the one I have implemented?