Business Question 1: How much sales percentage does each product sub-category take aganist the total sales of ‘Components’ cateogry in 2007

In the example above, the .parent is used to return the current product’s parent category. Alternatively, ANCESTOR function can also be used which could specify the level of the category (e.g, we hope to get the percentage of current product aganist top-level product category).

WITH
MEMBER [Measures].[Product Sale] AS
SUM(
[Product].[Product Categories].currentmember,
[Measures].[Reseller Sales Amount]
)

MEMBER [Measures].[Total Sale in Category] AS
AGGREGATE(
[Product].[Product Categories].currentmember.parent,
[Measures].[Reseller Sales Amount]
)

MEMBER [Measures].[Sales % of Parent Category] AS
IIF ([Measures].[Total Sale in Category]=0,
NULL,
[Measures].[Product Sale]
/[Measures].[Total Sale in Category]
)
, FORMAT_STRING="##.#%"

SELECT
{
[Measures].[Product Sale],
[Measures].[Sales % of Parent Category]}  ON COLUMNS,
ORDER
([Product].[Product Categories].[SubCategory].members
,[Measures].[Sales % of Parent Category], BDESC) ON ROWS
FROM
(SELECT [Product].[Product Categories].[Category].&[2] ON COLUMNS
FROM [Adventure Works]
)

Where [Date].[Calendar].[Calendar Year].&[2007]

Business Question 2: List product sub-categories which make up 80% of total sales in 2007

We could acheive this using TOPPERCENT function.

WITH
MEMBER [Measures].[Product Sales] AS
AGGREGATE(
[Product].[Product Categories].currentmember,
[Measures].[Reseller Sales Amount]
)

MEMBER [Measures].[Total Sales in Category] AS
AGGREGATE(
[Product].[Product Categories].currentmember.parent,
[Measures].[Reseller Sales Amount]
)

MEMBER [Measures].[Sales % of Parent Category] AS
IIF ([Measures].[Total Sales in Category]=0,
NULL,
[Measures].[Product Sales]
/[Measures].[Total Sales in Category]
)
, FORMAT_STRING="##.#%"

SELECT
{
[Measures].[Product Sales],
[Measures].[Sales % of Parent Category]}  ON COLUMNS,
TOPPercent(
[Product].[Product Categories].[SubCategory].members
,80
,[Measures].[Sales % of Parent Category]) ON ROWS
FROM
(SELECT [Product].[Product Categories].[Category].&[2] ON COLUMNS
FROM [Adventure Works]
)
Where [Date].[Calendar].[Calendar Year].&[2007]

Business Question 3: ABC analysis on the product Sales in UK in 2007

We want to classfy the products based on their sales amount in UK in 2007 into three categories: ‘A’ (make up the top 5% of total sales), ‘B’ (make up the top %5-20%), ‘C’ (the others). Firstly, we need rank the products based on their sales amount in UK in 2007 and calculate the cumulative sales amount by the rank. Then, we need calculate the percentage of the cumulative sales amount for each product and check which category is fallen into.

WITH
SET [Products on Sales] AS
ORDER
(
EXISTING NONEMPTY ([Product].[Product Categories].[Product].MEMBERS,
[Measures].[Sales Amount])
, [Measures].[Sales Amount], BDESC
)

MEMBER [Measures].[Cumulative Sales Amount by Rank] AS
SUM (
HEAD ([Products on Sales]
, RANK ([Product].[Product Categories].CURRENTMEMBER,
[Products on Sales])
)
, [Measures].[Sales Amount]
)

MEMBER [Measures].[Cumulative Sales % of parent category] AS
[Measures].[Cumulative Sales Amount by Rank]
/SUM ([Products on Sales],[Measures].[Sales Amount])
, FORMAT_STRING = "percent"

Member test as
[Products on Sales].CURRENTMEMBER.LAG(1).name

MEMBER [Measures].[Classfication Category] AS
CASE
WHEN [Measures].[Cumulative Sales % of parent category]<=.05 THEN 'A'
WHEN [Measures].[Cumulative Sales % of parent category]<=.2 THEN 'B'
ELSE 'C'
END

SELECT {[Measures].[Sales Amount],[Measures].[Classfication Category]} ON COLUMNS,
[Products on Sales] ON ROWS

FROM [Adventure Works]

WHERE ([Date].[Calendar].[Calendar Year].&[2007]
,[Sales Territory].[Sales Territory].[Country].&[United Kingdom])

• SSIS/SSAS/SSRS/MDX