Calculated member: sum / count

# Calculated member: sum / count

• 19 Maret 2012 20:00

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])

### Semua Balasan

• 19 Maret 2012 22:42

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

• Disarankan sebagai Jawaban oleh 19 Maret 2012 22:42
• Ditandai sebagai Jawaban oleh 26 Maret 2012 20:27
•
• 21 Maret 2012 18:42

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.

• 26 Maret 2012 20:28