MDX - SQL Server Analysis Services 2012 Cube Development - page 219

[Geography].[Country].[All Successful
Countries]} ON 0,
{[Measures].[average unit price],
measures.[reseller sales amount]} ON 1
FROM [Adventure Works]
WHERE [Product].[Product
Categories].[Category].[Components]
Tip
Note that the AGGREGATE function uses the aggregation method applic-
able to each measure. In the previous example, AGGREGATE returns the
average of all the unit prices and sum of all the reseller sales amounts.
6. The final requirement of this section is to display the average unit price
and total reseller sales amount for successful countries during each month
between January 2007 and June 2008. You can define a range of hierarchy
members by using a colon ( : ) operator. The following query cross joins the
requested date range (defined in the my_range named set) and successful
countries on rows:
WITH MEMBER [measures].[average unit
price] AS IIF (measures.[reseller
transaction count]=0, "N/A",
[measures].[reseller unit price] /
measures.[reseller transaction count]),
FORMAT_STRING="Currency"
SET [Successful Reseller Countries] AS
FILTER([Geography].[Country].[All
Geographies].Children,
[measures].[reseller sales amount]>300000)
MEMBER [Geography].[Country].[All
Successful Countries] AS
AGGREGATE([successful Reseller Countries])
SET my_range AS