使用Cubeset在指定的时间段内创建10个项目列表

I am using Excel 2010 to create a list of the top 10 vendors by sales during a specified time period. The catch is that I need to consider only customers that are a member of a particular set. I found this article which has helped me get the Top 10 vendors for sales from all customers, but I'm struggling with how to sum only over the members of a particular set. I tried the Sum/CrossJoin example that is further down the page in the comments, but I was unable to get it to work. It could be that I'm pretty new at this and just don't understand which pieces need to go where. Here is what I have so far (my connection name is in cell M1):


All Customers (works perfectly):

我正在使用excel2010在指定的时间段内按销售额列出十大供应商。问题是,我只需要考虑客户的一组特定的成员。我发现了这篇文章,使我得到销售的十大供应商从所有客户,但我在如何和只有一组特定的成员。我试着之和/ CrossJoin例子进一步降低页面的评论,但我却无法让它工作。这可能是因为我在这个问题上是一个新手，只是不明白哪些东西需要去哪里。这里是我目前为止所拥有的(我的连接名称在cell M1中):所有客户(工作完美):

=CUBESET($M$1,
         "TopCount(
           [Product].[Brand].Children,
           10,
           Sum(
             [Time].[Calendar].[Calendar Month].&[2011]&[8],
             [Measures].[Revenue]
           )
         )",
         "Top 10 Brands"
)

Subset of Customers (appears to return correct set):

客户子集(似乎返回正确的集合):

=CUBESET($M$1,
         "Intersect(
           Intersect(
             exists(
               [Customer].[Cust Num].Members,
               {[Customer].[Is Internal].&[False],[Customer].[Is Internal].[All].UNKNOWNMEMBER}
             ),
             exists(
               [Customer].[Cust Num].Members,
               [Customer].[Type].&[CAT]
             ),
             ALL
           ),
           exists(
             [Customer].[Cust Num].Members,
             [Market].[Market ID].[All].Children - [Market].[Market ID].&[3] - [Market].[Market ID].&[4]
           ),
           ALL
         )",
         "Cust Group"
)

Any help and/or guidance would be greatly appreciated.....thanks in advance!

如有任何帮助和/或指导，我们将非常感谢……提前谢谢!

1 个解决方案

#1

You could try something like this

你可以试试这个

=CUBESET($M$1,
     "TopCount(
       [Product].[Brand].Children,
       10,
       Sum(
            (
                [Time].[Calendar].[Calendar Month].&[2011]&[8],
                [Customer].[Is Internal].&[False],
                [Customer].[Type].&[CAT]
            ),
         [Measures].[Revenue]
       )
     )",
     "Top 10 Brands"
)