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

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)
SELECT [Successful Reseller Countries] ON
0,
{[Measures].[average unit price],
measures.[reseller sales amount]} ON 1
FROM [Adventure Works]
WHERE [Product].[Product
Categories].[Category].[Components]
5. The query in the previous step shows the output for each successful (in terms
of reseller sales) country. Let's take it one step further to show the total num-
ber of reseller sales as well as the average unit price for all the successful
countries combined. Use the AGGREGATE function to derive measure values
for the Successful Reseller Countries named set as follows:
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])
SELECT {[Successful Reseller Countries],