Hi
I have three dimensions named Product, Time, City and one measure sales.
I want to know the average sales made in a particular city.

Assume the product has only one level, City has one level city, and time has
two level month and day.
A simple SQL would look like this-
select avg(sales) from sales_fact where intcityid=5
how can i do this using MDX.

Create a calculated member which is a count. Then have a calculated member
which is Sales/Count. Look in Foodmart at the Profit calculated member -- it
is Sales minus cost; your "avg" calculated member would be Sales divided by
count. If you want, you can then hide the Count member so users dont' see
it.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"Hari" <Hari (AT) discussions (DOT) microsoft.com> wrote

Thanks for the help Dave , but can't this be done using the avg() function of
MDX. Should i have to create a measure for tracking the count. Or can i
create this measure in the MDX itself and still get the result?


"Dave Wickert [MSFT]" wrote:

MSAS does not provide capability to preaggregate data by averaging.
As Dave said below you will frequently calculate averages by dividing
a sum by a count.

"AVG() takes the everage of the non-empty values found across cells
related to the set." G. Spofford


"Dave Wickert [MSFT]" <dwickert (AT) online (DOT) microsoft.com> wrote

You can calculate number of members on the fly thusly:

avg(nonemptycrossjoin(Descendants([dim1].CurrentMember,[dim1].[lev1]),descendants([Time].[ymd].CurrentMember,[Time].[ymd].[Day]),descendants([Time].[hm].CurrentMember,[Time].[hm].[Minute])),[Measures].[meas1])

Possibly this is much more inefficient than using a count, comments
anyone???

Cheers



"Michael" <michael.hardy (AT) kerzner (DOT) com> wrote

MSAS does not provide capability to preaggregate data by averaging.
As Dave said below you will frequently calculate averages by dividing
a sum by a count.

"AVG() takes the everage of the non-empty values found across cells
related to the set." G. Spofford


"Dave Wickert [MSFT]" <dwickert (AT) online (DOT) microsoft.com> wrote