8. For each product brand, what are the two top-selling products and what percentage of total sales do they make up?
To answer this question, Listing 8's relatively complicated query uses a combination of a calculated member and the Generate() function. The calculated member determines the percentage of the brand's total unit sales that a brand's top two products make up. The Generate() function searches the list of all brands and returns a set of each brand's top two products and the percent of total value that those products account for.
查出每一种品牌销售最好的两种产品的销量额,以及分别占销售总额的百分比。 Listing8相对比较复杂,综合运用了计算成员和Generate()函数。计算成员确定了每个品牌销售最好的前两个产品占所在品牌unit sales总量百分比。Generate函数查询每个品牌并返回每个品牌下销售最好的两个产品,以及每个产品的销售额和百分比。
Listing_08.Determining Two Top-Selling Products.txt
说明:查出每种品牌
前
2
名
产品的销售记录,以及各自分别占所在品牌的百分比
with
member
[Measures].[PercTotalSales]
as
'
Sum
(
TopCount
([Product]
.CurrentMember.Children
, 2, [Unit Sales]), [Unit Sales] )
/([Product]
.CurrentMember
, [Unit Sales])',
FORMAT_STRING
= '##.0%'
select
[Store].[(All)]
.Members
on
COLUMNS
,
Generate
( [Product].[Brand Name]
.Members
,
Union
(
TopCount
( [Product]
.CurrentMember.Children
, 2, [Unit Sales] ) * {[Unit Sales]},
{ ([Product]
.CurrentMember