Help on Calculated Members

Hi Gurus,

I am new to Microsoft Analysis Services. I wanted to create a new Average Calculation.

The Syntax is

Avg(?Set[, ?Numeric Expression]). I am using the Foodmart cube.

I have tried using many things but have not achieved any success.

These are the following things that I have tried.

1. Avg([Product].[Product Family].&[Drink].&[Dairy].&[Dairy].&[Milk] , 'Avergare of Milk')

In this 'Avergare of Milk' is some sort of Calculation name I suppose.

2. Avg([Product Family].&[Drink].&[Dairy].&[Dairy].&[Milk])

In this ‘6’ I have mentioned assuming there are only 6 members in the hierarchy.

Please tell me What shall I write in place of Set and Numeric Expression.

I also came across an article and found this

“If you want to create an average, what you need to do is create one
measure
called SUM, and another measure based on COUNT and then create a
calculated
member called AVG which would just be SUM/COUNT. I believe there are
examples of this in the Foodmart 2000 cubes.”

But to my Surprise I also found the Syntax of SUM and Count to be similar.

Please help me out in this.

I also have a request Could anyone give me some examples and any link from where I could learn all the available calculation with examples.

Thanks in advance

Here is an example that should help you:

with member [Measures].[Av1] as
'Avg({descendants([Product].[All Products].[Drink],[Product Name])}, [Measures].[Profit])'
member [Measures].[Av2] as 'Avg({descendants([Product].[All Products].[Drink],[Product Name])}, coalesceempty([Measures].[Profit],0))'
member [Measures].[Num] as 'count({descendants([Product].[All Products].[Drink],[Product Name])})'
member [Measures].[Sum] as 'sum({descendants([Product].[All Products].[Drink],[Product Name])}, [Measures].[Profit])'
member [Measures].[Av3] as '[Measures].[Sum]/[Measures].[Num]'

select

{[Measures].[Av1],[Measures].[Av2],[Measures].[Av3],[Measures].[Num],[Measures].[Sum]} on columns,

{[Store].[Store Name].members} on rows
from sales

As you can see, you Avg1 is different from Av2 because it doesn't consider empty cells, so the average is slightly bigger.

Hope this help,

Santiago

|||

Hi,

Thanks for the help. But I think I was not clear what I wanted. I am using Analysis manager (Microsoft SQL Server -> Analysis Services -> Analysis Manger).

In that I am drilling down the Analysis Servers to list the Available servers and then drilling down the database present to get the list of the available cubes.Now I am right clicking on a cube and choosing edit to open the Cube Editor.In Cube editor I am trying to create a new Calculated Members. (In the Calculated member Builder I am unable to write the query for the Average.) There I am not able to make the calculation. And am unable to sort out the problem.

I hope this information help in giving my problem the perfect picture.

Please let me know if u need anything else

Thanks in advance

|||

In Calculated Member builder, select Measures as the Parent Dimension, write the name of the measure (eg. MyAverage) and type the appropriate mdx expression. For example:

Avg({descendants([Product].[All Products].[Drink],[Product Name])}, [Measures].[Profit])

You don′t need then to reprocess the cube. Just browse it and you should be able to see the new Calculated Member.

|||

HI,

Many many thanks for the help. It worked.

Can I get some link from where I can get some examples of the calculation.

Thanks