I wonder why AS doesn't provide the 'AVERAGE' as aggregation function for a
regular measure, though its supported my Sql server.
Is there any specific reason for this.

Thanks,
Sumanta

http://groups-beta.google.com/group/...rver.olap/brow
se_frm/thread/fc4f960fe027fbed/488aa4ed96f3faf4#488aa4ed96f3faf4

Newsgroups: microsoft.public.sqlserver.olap,
microsoft.public.sqlserver.datawarehouse
From: "MPS" <m... (AT) udd (DOT) cl> - Find messages by this author
Date: Wed, 30 Mar 2005 09:38:22 -0400
Local: Wed,Mar 30 2005 8:38 am
Subject: Help with measure aggregation functions
Reply to Author | Forward | Print | Individual Message | Show original |
Report Abuse

Hi, I will rewrite my question.

I'm having trouble to show averages of a measure in a cube, where the
normal
aggregation function for a measure is SUM.

I see no AVG aggregation function for measures (I see Min, Max, Count,
Distinct Count and SUM).

If I hide the measure (cost), and create a calculated member based on
that
measure, ie Avg(cost), I have te problem of how to average it, since the
cube has two dimensions in the row axis, like:

Time
------------------------------*---------
Product |
Customer | avg of cost

If I use avg(nonemptycrossjoin(product.*currentmember.child ren,
customer.currentmember.childre*n), measures.cost) I get the same average
for
every cell in the cube, what's not corrrect...

Sorry to bother you all, but this thing is becoming a nightmare.

Hope you can help

Michael Prendergast

OLAPMonkey Mar 30, 12:11 pm show options

Newsgroups: microsoft.public.sqlserver.olap,
microsoft.public.sqlserver.datawarehouse
From: "OLAPMonkey" <jja... (AT) spss (DOT) com> - Find messages by this author
Date: 30 Mar 2005 09:11:32 -0800
Local: Wed,Mar 30 2005 12:11 pm
Subject: Re: Help with measure aggregation functions
Reply to Author | Forward | Print | Individual Message | Show original |
Report Abuse

Averages are usually handled by summing and counting...and then
dividing the sub by the count in a calculated member.

MPS Mar 30, 3:08 pm show options

Newsgroups: microsoft.public.sqlserver.olap,
microsoft.public.sqlserver.datawarehouse
From: "MPS" <m... (AT) udd (DOT) cl> - Find messages by this author
Date: Wed, 30 Mar 2005 16:08:26 -0400
Local: Wed,Mar 30 2005 3:08 pm
Subject: Re: Help with measure aggregation functions
Reply to Author | Forward | Print | Individual Message | Show original |
Report Abuse

Sometimes, getting back to basics gets the job done

Thank yo very much, problem solved

Michael

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

I would guess because if it did...it would often not be the average
folks really want. In Yukon...an Average The Children option has been
added, but this is the average that folks don't really want.

Think of a geography dimension...let's say you had 2 states...Oregon
and Washington...and Washington had 100,000 children cities and Oregon
had 1. When you look at the Average at the state level Washington has
an average based on 100,000 children and Oregon's is based on one. If
you then go up one level...to see the Northwest Region...it would be
the average of the 2 states...both treated as equals. A weighted
average, however, would be based on the 100,001 children records in the
region and it would average them. You achieve the weighted average by
summing and by counting and by dividing you sum by the count in a
calculated member.