Newbie Question

Last Post 14 Sep 2004 11:04 AM by baolinren. 6 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

08 Sep 2004 02:02 PM
My cube has 3 dimensions. each dimension has quite a few levels. If I don't drill down the levels, the measures will only show up the sum of all values under that level by the default aggregate function, but what I actually want is the average of the values, not sum. For example...while browsing my cube:

Week1 total: 1000
Monday: 100
Tuesday: 110
Wednesday: 120

However, average is not one of the 5 aggregate functions that analysis manager provides, how do i write my own, so the Week 1 total can become Week1 Average

New Member
New Member

14 Sep 2004 11:04 AM
Check calculated members in books online.
New Member
New Member

14 Sep 2004 11:56 AM
is it possible to give me more details? I can't find a solution from online books yet.

Simplifying my problem:

when u drill down levels in a cube, what u see is like

Week 1 Total: 1000
Monday: 100
Tuesday: 120
Wendesday: 130

The first row is always the total, or one of the few aggregate functions Microsoft provided us.
What I want is to make my own aggregate function, so instead of simply doing totals, can do some other type of average
New Member
New Member

16 Sep 2004 08:06 AM
You can always create a calculated measure using MDX.
New Member
New Member

16 Sep 2004 02:04 PM
I still can't solve with the hint: calculated members/measure

Step 1: I add new calculated member
Step 2: Date dimension?
Step 3: What MDX expression?

Here's my cube with measures and Date dimension breaks down to daily

-2004 -Q1 -Jan -Week1 - Week1 total:__1000_100____rate*count

cal is calculated measure with formula rate*count, BUT for 1st row, that rate for that row is wrong because Microsoft created this aggregation function (sum) and gave me a total, not an average! i want that value "1000" to become the average "333", but i can't change the aggregate functions, nor can replace that automatic total row with my calculated member...

New Member
New Member

21 Sep 2004 08:18 AM
Here is what I have done:

iif ([Ship Dates].[Calendar_Desc].CurrentMember.Level is [Ship Dates].[Calendar_Desc].[Day],
// Calculate only the Year, Quarter and Month levels
iif ([Measures].[SalesDays] = 0,null,
[Measures].[Sales in USD]/[Measures].[SalesDays]))

That is my calculation for Average Daily Sales. I have a hierarchy over my ship dates dimension that puts the years in descending order. In this dimension, I have Year, Quarter, Month, and Day levels.

I have member properties at all of the levels. At the Year, Quarter, and Month levels, I have the number of business days in that period. At the day level, I have the day of the week (numerically and alphabetically) as member properties.

The SalesDays measure is also a calculated member. Its formula is below:

iif ([Ship Dates].[Calendar_desc].CurrentMember.Level is [Ship Dates].[Calendar_Desc].[Year],
// Current Member is a Year
// Sum it's descendants at the Qtr Level
Sum(Descendants([Ship Dates].[Calendar_desc].CurrentMember,[Ship Dates].[Calendar_Desc].[Quarter])),
// Else not a Year
iif ([Ship Dates].[Calendar_desc].CurrentMember.Level is [Ship Dates].[Calendar_Desc].[Quarter],
// Current Member is a Quarter
// Sum it's descendants at the Month Level
Sum(Descendants([Ship Dates].[Calendar_desc].CurrentMember,[Ship Dates].[Calendar_Desc].[Month])),
// Else not a Quarter
iif ([Ship Dates].[Calendar_desc].CurrentMember.Level is [Ship Dates].[Calendar_Desc].[Month],
// Current Member is a Month - Test to see if it's the current month
// {[Ship Dates].[Calendar_Desc].[Year].&[-2004].&[2].&[6]}
// iif (intersect({[Ship Dates].[Calendar_desc].CurrentMember}, {[CurrMonth]}).count > 0,
iif (intersect({[Ship Dates].[Calendar_desc].CurrentMember},
{[Ship Dates].[Calendar_Desc].[Year].&[-2004].&[3].&[9]}).count > 0,
// the line above was put in to avoid using CurrMonth & should be updated monthly
// Current Month - Sum the this members leaves to get a number of actual
// sales days in the month
Sum(Descendants([Ship Dates].[Calendar_desc].CurrentMember,,Leaves)),
// Else - Use the plug number stored as a member property
StrToVal([Ship Dates].[Calendar_desc].CurrentMember.Properties("monslsdays"))),
// Else not a Month
iif ([Ship Dates].[Calendar_desc].CurrentMember.Level is [Ship Dates].[Calendar_Desc].[Day]
[Ship Dates].[Calendar_desc].CurrentMember.Properties("wkday") <> "Saturday" and
[Ship Dates].[Calendar_desc].CurrentMember.Properties("wkday") <> "Sunday"
// Current Member is a Day - If the day has sales then set to 1 so that when
// summed to the month level will give a count of the number of actual days
// with sales.
iif ([Measures].[Sales in USD] <> 0,1,0),

Hope this helps.
New Member
New Member

21 Sep 2004 09:02 AM
Thanks, this helps a lot!...thank you for solving the problem that had been bothering me for days.

Acceptable Use Policy