19 Maret 2012 20:00
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.
"sum of" ([CaseStatus A],[MeasureNo 1100],[Measures].[CaseResponseTime]) / "count of" ([CaseStatus A],[MeasureNo 1100],[Measures].[CaseResponseTime])
19 Maret 2012 22:42
1. For Sum :
MEMEBR [Measures].[TheSum] AS
Say "measure group" of [Measures].[CaseResponseTime]is "Measure Group 1"
Create a separate measures for distinct count
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
F. Now your average will be
MEMEBR [Measures].[TheAverage] AS
Hope this helps,
Note : If this is helpful, Do not forget to mark as "Answered" or "Helpful"
21 Maret 2012 18:42
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
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?