Mosha , I do have a question though. I am trying to calculate Average Seperation Count. I have an MDX that works properly at the quarterly level when I run the query in MDX, but does not work properly in the cube as a calculated metric. It is almost like I need to set the AggregateFunction on the calculated metric but I cant figure out how.
Here is the scenario:
My Mdx is:
With
MEMBER [Measures].[AVG SEP CNT] ASROUND(
AVG([Time Hierarchy].CurrentMember.Children, [Measures].[SEP CNT]), 4 ) MEMBER [Measures].[AVG EMP HD CNT] ASROUND(
AVG([Time Hierarchy].CurrentMember.Children, [Measures].[EMP HD CNT]) , 4) MEMBER [Measures].[AVG SEP RT] ASROUND( [Measures].[AVG SEP CNT] / [Measures].[AVG EMP HD CNT] , 4)
select
{
[Measures].[SEP CNT]
, [Measures].[EMP HD CNT]
, [Measures].[AVG SEP CNT]
, [Measures].[AVG EMP HD CNT]
, [Measures].[AVG SEP RT]
}
ON COLUMNS, [Time Hierarchy].[Quarter].MEMBERS ON ROWS
FROM
[Cube1]
Results are:
SEP_CNT | EMP_HD_CNT | AVG_SEP_CNT | AVG_EMP_HD_CNT | AVG_SEP_RT | |
Quarter 1, 2006 | 67 | 8503 | 67 | 8503 | 0.0079 |
Quarter 2, 2006 | 172 | 8316 | 57.3333 | 8367.6667 | 0.0069 |
The problem that I have is that when I put the calculations from the WITH statement into my cube I get the correct answer in the totals section for the quarter, but the Yearly totals section is taking the sum of the AVG_SEP_CNT field for the month and then is dividing the total by 2. The green totals are correct / red are not. I haven't figure out what is going on with AVG_EMP_HD_CNT yet.
I copied this from the cube browser:
SEP_CNT
EMP_HD_CNT
AVG_SEP_CNT
AVG_EMP_HD_CNT
AVG_SEP_RT
Calendar 2006
Quarter 1, 2006
January 2006
0
0
-1.#IND
February 2006
0
0
-1.#IND
March 2006
67
8503
67
8503
0.0079
Total
67
8503
67
8503
0.0079
Quarter 2, 2006
April 2006
73
8422
73
8422
0.0087
May 2006
58
8365
58
8365
0.0069
June 2006
41
8316
41
8316
0.0049
Total
172
8316
57.3333
8367.6667
0.0069
Quarter 3, 2006
0
0
-1.#IND
Quarter 4, 2006
0
0
-1.#IND
Total
239
8316
119.5
8409.5
0.0142
I know this has something to do with the CurrentMember.Children property, removing the .Children part gave me the wrong answer entirely. What I need is the average of the two quarters (67+57.33) divided by 2 which is 62.165.
I am thinking maybe I need some case logic or hide the total at the year level and have another calculated column for the yearly 'total'. What do you think
Thanks in advance,
Sean