PowerPivot基于组的汇总值 [英] PowerPivot Aggregated value based on groups

问题描述

我在一个表中有两个日期列

酷炫听觉：活动|城市|开始日期|结束日期

第1行：A1 | C1 | 2014年1月1日| 2014年5月1日

第2行：A1 | C1 | 2014年6月1日| 07/01 / 2014

第3行：A2 | C2 | 06/01/2014 | 07/01/2014

第4行：A3 | C3 | 03/01/2014 | 04/01/2014

预期的输出，例如-如果用户选择日期范围为2014年2月1日到2014年7月2014年1月1日

列标题

城市| #StartCount | #EndCount

第1行：C1 | 1 | 2

第2行：C2 | 1 | 1

第2行：C3 | 1 | 1

此处#StartCount为
-需要按城市分组。
-其不同活动的计数
-应将日期限制视为：开始日期为更大或等于（> =）'输入开始日期且（小于'<'）结束的所有活动日期

此处#EndCount为
-需要按城市分组。
-其不同活动的计数
-应将日期限制视为：所有结束日期为'Less or equeal（< =）'输入结束日期且（大于'>'）End的活动日期

您能建议我用这种表达方式吗？

解决方案

此处的关键是使用断开的切片器，因为如果使用了常规变量，然后执行COUNT计算将不起作用，因为它们只是过滤掉不适合的行。

我已经使用了您的数据用于创建3个表：

• Source 表-与您的表完全相同


• 开始表-包含名为 Start 的单个列，其中包括来自 Source


• 的开始日期 End 表-包含一个名为 End 的列，其中包括 Source

$ b的结束日期
$ b

然后我添加了两个计算字段：

开始计数

  = CALCULATE（COUNTA（Source [City]），FILTER（Source，Source [Start]> = MIN（'Start'[Start]）））
  

结束计数

  = CALCULATE（COUNTA（Source [City]），FILTER（Source，Source [Start]< = MIN（'End'[End]）））
  

这两个命令的作用是对不为空的城市行进行计数，并通过（使用FILTER命令） Start 或对其进行过滤结束列。阅读有关（免费）或预先准备的DateTables ，您应该没问题。

希望这会有所帮助！

I have two date columns in one table

Coolumn Hears : Activity|City|Start_Date|End_Date

Row 1 : A1|C1|01/01/2014|05/01/2014

Row 2 : A1|C1|06/01/2014|07/01/2014

Row 3: A2|C2|06/01/2014|07/01/2014

Row 4: A3|C3|03/01/2014|04/01/2014

Expected output like - If user selects date range 02/01/2014 to 07/01/2014

Column Header

City | #StartCount| #EndCount

Row 1 : C1 | 1 | 2

Row 2 : C2 | 1 | 1

Row 2 : C3 | 1 | 1

Here #StartCount is - Need to grouped by City. - Its Count of Distinct Activity - It should consider date boundries as : All activity for which start date 'greater or equeal(>=)' Input Start date and (less than '<') End Date

Here #EndCount is - Need to grouped by City. - Its Count of Distinct Activity - It should consider date boundries as : All activity for which Ends date 'Less or equeal(<=)' Input End date and (Greater than '>') End Date

Could you please suggest me expression to be used for such case. Calculated measure or dax can be used..

解决方案

The key here is using disconnected slicers, because if you used the "regular" ones, then performing the COUNT calculations won't work because they simply filter out the rows that don't "fit".

I have used your data for creating 3 tables:

• Source table - exactly the same as yours
• Start table - containing single column called Start that includes the start dates from Source
• End table - containing single column called End that includes the end dates from Source

Then I added two calculated fields:

Start Count

=CALCULATE(COUNTA(Source[City]),FILTER(Source,Source[Start]>=MIN('Start'[Start])))

End Count

=CALCULATE(COUNTA(Source[City]),FILTER(Source,Source[Start]<=MIN('End'[End])))

What these two commands do is that they count City rows that are not empty, and filter them by (using FILTER command) Start or End column. Read more about disconnected slicers here.

To get your report: create a new PowerPivot table, put City on rows and drag Start Count and End Count to values. Then add slicers for both Start and End tables. Selected your dates and you will get the results you need.

Attached is the screen of what I used (this will work in Excel 2013 as well as Excel 2010 with PowerPivot plugin):

Using disconnected slicer might be tricky sometimes -- but if you create them with newly available Excel Apps like CreateTimeDimension (for free) or pre-prepared DateTables, you should be just fine.

Hope this helps!

这篇关于PowerPivot基于组的汇总值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！