On the ROWS axis, show the Actual Sales for the states {WA, OR, CA} as a percentage of the sales in the rest of the USA (except Hawaii and Alaska) for the following: All Products in the Applications Division, All Versions of GreatOffice and Database 96. On the COLUMNS axis, show all quarters and all months for 1997.
The MDX statement that retrieves this dataset is
WITH MEMBER
USA.[North West Ratio] AS SUM({WA, OR, CA}, Sales) /
SUM(EXCEPT(USA.CHILDREN, {Hawai, Alaska}), Sales)
SELECT HIERARCHIZE({[1997], [1997].CHILDREN, DESCENDANTS([1997],
Months)}) ON ROWS,
{[Applications Division] , GreatOffice.CHILDREN,
[Database 96] } ON COLUMNS
FROM SalesCube
WHERE ([North West Ratio], Actual)
The WITH MEMBER clause defines a new member called North West Ratio. This member is obtained by taking the percentage of the sum of Sales for the states OR, WA, and CA, and dividing this by the sales for all states in the USA except Alaska and Hawaii. By qualifying the name of this member with USA, the data source recognizes that this should be on the Geography dimension.