Powerpivot将计算的项目添加到字段 [英] Powerpivot adding calculated item to a field

问题描述

我在存储在Excel文件中的表中有数据。我将此表链接到PowerPivot数据模型中，然后从该数据模型中我要在同一Excel文件中创建一个Pivot。在此表中，一列包含以下数据划分：预算，去年，先前预测，当前预测。我想将此字段添加为Pivot coulmns，但我想添加其他计算项目（例如在普通的Excel Pivot表中，我可以添加计算项目），其计算如下：[当前预测]-[先前预测]。我检查了各种页面，论坛等，但没有找到有关如何将此类计算项添加到PowerPivot中的字段的任何指导。
我的输入看起来像这样：

 样本数据：
类别客户金额
当前预测XYZ 600 
当前预测ABC 1000 
当前预测DEF 100 
先前预测XYZ 500 
先前预测ABC 1200 
预算XYZ 800 
预算ABC 900 
预算DEF 100 
去年XYZ 700 
去年ABC 500 
  

根据此数据，我想创建一个如下所示的数据透视表：

 当前先前的上一个
客户预测预测预算年份同比
 XYZ 600500800700100 -100 
 ABC 1000 1200900500 -200500 
 DEF 100100100100 
  

在PowerPivot中，我要添加tw o其他列，可能是类别字段中的计算项：

  FoF = [当前预测]-[先前预测] 
 YoY = [当前预测]-[去年] 
  

在下面的屏幕截图中，可以更清楚地看到，我想要实现的目标：

我无法将其添加到我的源数据中，因为当前预测和先前预测之间的行数不相同且不在相同的顺序（它们是从另一个系统中提取的）

解决方案

Guenh ，这应该很简单：

根据您的PowerPivot版本，您可以创建一个新的通过PowerPivot功能区进行测量（Excel 2010外接程序：新测量按钮）。在Excel 2013中，它已重命名为 Calculated Field ，仍然可以通过PowerPivot功能区使用：

无论如何，我下载了您的数据并创建了新的测量值（=计算字段）您所有的列，这是关键。数据的结构方式并不理想，但肯定是可行的。首先，您必须汇总每个类别的金额，因此请使用以下简单的 CALCULATE 公式：

 - CALCULATE（SUM（[Amount]），Table1 [Category] ​​= Budget）
  

是它对金额值求和，但仅对于在类别列中具有预算的行。上面的示例是为预算类别定义的；也包括其他三个类别（只需复制公式并在 CALCULATE 中更改第二个参数：代替预算，使用去年，等等。

然后只需使用已创建的度量来创建 FoF 和 YoY 计算：

< img src = https://i.stack.imgur.com/g66df.png alt =在此处输入图片描述>

如果您再添加新的数据透视表（从PowerPivot窗口中），只需将 Client Name （客户名称）拖到行部分，并将所有度量拖到 Values 部分。

这样，您应该得到所需的结果表，如下所示。在这里您可以下载源文件（适用于Excel 2013）。

希望这会有所帮助：）

I have data in a table stored in an Excel file. I linked this table into a PowerPivot Data Model, and from that Data Model I want to create a Pivot in the same Excel file. In this table one column contains split of data into: Budget, Last Year, Prior Forecast, Current Forecast. I want to add this field as Pivot coulmns, but I would like to add additional calc item (like in normal Excel Pivot Table I can add Calculated Item) with calculation: [Current Forecast] - [Prior Forecast]. I checked various pages, forums, etc. and I have not found any guidance on how to add such a calc item to a field in PowerPivot. My input looks like this:

Sample data:
Category           Client   Amount
Current Forecast    XYZ        600
Current Forecast    ABC       1000
Current Forecast    DEF        100
Prior Forecast      XYZ        500
Prior Forecast      ABC       1200
Budget              XYZ        800
Budget              ABC        900
Budget              DEF        100
Last Year           XYZ        700
Last Year           ABC        500

From this data I want to create a Pivot that would look like this:

        Current    Prior               Last
Client  Forecast   Forecast   Budget   Year    FoF    YoY
XYZ          600        500      800    700    100   -100
ABC         1000       1200      900    500   -200    500
DEF          100                 100           100    100

In PowerPivot, I want to add two additional columns, perhaps as calculated items in the Category field:

FoF=[Current Forecast]-[Prior Forecast]
YoY=[Current Forecast]-[Last Year]

On below screenshots it is better visible, what I want to achieve:

I can't add this to my source data as the number of rows between current forecast and prior forecast are not in the same amount and not in the same order (they are extracted from another system)

解决方案

Guenh, this should be quite straightforward:

Depending on your PowerPivot versions, you either create a new measure from PowerPivot ribbon (Excel 2010 Add-in: New Measure button). In Excel 2013, it was renamed to Calculated Field, still available via PowerPivot ribbon:

Anyway, I downloaded your data and created new measures (= calculated fields) for all of your columns, which was the key. The way your data is structured is not ideal, but it's certainly doable. First you have to sum the amount for each category, so use this simple CALCULATE formula:

-CALCULATE(SUM([Amount]),Table1[Category]="Budget")

What it does is that it sums the amount values, but only for rows have Budget in the Category column. The example above is defined for the Budget category; include the other three categories as well (simply duplicate the formula and change the second parameter in CALCULATE: instead of "Budget", use "Last Year", etc.

Then create your FoF and YoY calculations by simply using already created measures:

If you then add new PivotTable (from PowerPivot window), simply drag Client Name to rows section and all the measures to Values section.

With that, you should get your desired result table as shows below. Here you can download the source file (for Excel 2013).

Hope this helps :)

这篇关于Powerpivot将计算的项目添加到字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！