Excel PowerPivot 平均计算

Question

我在确定如何计算可以绘制在数据透视图上的平均值时遇到了一些困难。

具体来说，我希望将销售代表的业绩(按月/年计算的毛利润)与在给定时期内担任可比角色(同一工作组)的所有其他销售代表(使用平均值)进行比较。

假设数据结构如下:

SaleID      SaleLocation        SaleType        SalesRep        SaleDate        WorkGroup       SalesGP
1           Retail1             Car             John A          01/01/2014      Sales           $301
2           HQ                  Bike            John A          01/01/2014      Sales           $200
3           Retail1             Car             Sam L           02/01/2014      Sales           $1300
4           Retail2             Plane           Sam L           02/01/2014      Sales           $72
5           Retail2             Plane           Vince T         03/01/2014      Admin           $55
6           Retail2             Bike            John A          04/01/2014      Sales           $39
7           HQ                  Car             Vince T         05/01/2014      Admin           $2154
....etc

在 excel 数据模型中，我为销售日期添加了计算字段(使用查找表)，以便可以按月或年绘制销售额(例如 =YEAR([SaleDate]) 和 =MONTH([SaleDate]) )

例如，假设我想绘制一段时间内某人的 GP(毛利润):



我的问题是这个......

如何计算可以在数据透视图上绘制的“平均毛利润”？这个“平均毛利润”应该是同一工作组在同一时期的所有销售额的平均值。

在上面的示例中，在数据透视图中，我想绘制一个“平均”系列，该系列按月绘制与 John A(“销售”)在同一工作组中的所有销售代表的平均 GP。

如果我的要求不够明确，请告诉我，我会尽力扩展。

Answer 1

Zam，这应该很容易。 您只需要创建一个新的计算字段来计算所有销售代表的平均值 .

让我带您了解一下:

我使用了您的数据表，然后将其添加到我的 PowerPivot (Excel 2013)。然后我创建了这些计算的度量:

1. 平均销售额 :

=AVERAGE(SalesData[SalesGP])

2. 平均销售额 ALL -- 这将计算表中所有行的平均值，并将用于其他计算。请注意，我使用第一个计算字段作为第一个参数以使其灵活:

=CALCULATE([Sales Amount Average],ALL(SalesData))

3. 销售额与平均水平的比较 .我不确定你的目标是什么，但我让这个更复杂一点，因为我想以百分比显示性能:

=IF([Sales Amount Average] > 0, [Sales Amount Average] / [Sales Average ALL] -1)

基本上，它的作用是首先检查他们是否对销售代表进行了现有计算，如果是，则将给定销售代表的平均销售额除以所有销售代表的平均销售额。然后我减去 1，因此只需查看百分比即可轻松掌握性能。

为了使其易于理解，我决定使用条件格式的条形图而不是独立的数据透视图——我相信它完全符合您的需要:



在上图中，第一个表代表您的数据。第二个表是实际的 powerpivot 表，显示了我所描述的内容。

希望这可以帮助!

编辑

我不想让事情变得过于复杂，但如果您想从总计行中删除百分比总计，请使用此计算代替百分比比较:

=IF(HASONEVALUE(SalesData[SalesRep]),
    IF([Sales Amount Average] > 0, 
        [Sales Amount Average] / [Sales Average ALL] -1),
    BLANK()
)

编辑 - 增加了工作组的平均比较

要计算每个工作组而不是所有销售代表的绩效，请添加这两个度量:

4. 每个工作组的平均销售额

=CALCULATE(AVERAGE(SalesData[SalesGP]),ALL(SalesData[SalesRep]))

这将计算每个工作组的平均销售额(不要与在 ALL 函数中使用 SalesRep 混淆，它与过滤上下文有关)。

5. 每个工作组的平均销售额差异

=IF(HASONEVALUE(SalesData[SalesRep]),IF([Sales Amount Average] > 0, [Sales Amount Average] - [Sales Average per Workgroup]),BLANK())

这只是计算给定销售代表的平均销售额与每个工作组的平均销售额之间的差异。结果可能如下所示:



我已经上传了 source file to my public Dropbox folder .