如何将计算列添加到连接到SSAS表模型的枢轴表中?

Or in another words the question is - how to add some calculation in the pivot table based on columns which do not exist in model level.

或者换句话说，问题是——如何基于模型级不存在的列在pivot表中添加一些计算。

I've reproduced my problem using AdventureWorksDW2014 sample database.

我使用AdventureWorksDW2014示例数据库重现了我的问题。

Let's say I want to calculate difference between Actual and Budget scenario amounts in the FactFinance table for each Organisation and present it in a form of pivot table.

假设我想要计算每个组织的FactFinance表中实际和预算场景数量之间的差异，并以数据透视表的形式呈现出来。

To achieve that I've created a simple model (screen above) and added SumOfAmount measure to the FactFinance table SumOfAmount:=SUM([Amount])

为了实现这一点，我创建了一个简单的模型(上面的屏幕)，并将SumOfAmount方法添加到FactFinance表SumOfAmount:=SUM([Amount])

Next, I've opened my model in Excel and created very simple pivot table (shown below)

接下来，我在Excel中打开了模型并创建了非常简单的pivot表(如下所示)

So, (the question part) now I want to add an extra column to my pivot table, which should calculate something (for example difference) between columns Actual and Budget. And I want this new column been a part of the pivot table so I could filter it or\and add new grouping levels without necessity to change something "outside" the pivot table.

所以，(问题部分)现在我想在pivot表中添加一个额外的列，它应该在列实际和预算之间计算一些东西(例如差异)。我希望这个新列是pivot表的一部分，这样我就可以对它进行过滤或者添加新的分组级别，而不需要在pivot表之外进行更改。

TRIED SO FAR

尝试到目前为止

I tried to add Calculated Field but it seems like I can only use "real" columns for calculations. Columns which appeared in a pivot table based on values from COLUMNS quadrant can't be used as sources for calculations.

我试图添加计算字段，但似乎只能使用“real”列进行计算。基于列象限的值出现在pivot表中的列不能用作计算的源。

FINAL SOLUTION

最终的解决方案

I got it finally combined two pivot tables: the old one and the one with Diff measure, defined as Diff:=[Actual Amount]-[Budget Amount], where

最后我得到了两个数据透视表:一个是旧的，另一个是Diff测度，定义为Diff:=[实际金额]-[预算金额]，其中

Actual Amount:=Calculate([SumOfAmount];'DimScenario'[ScenarioName] = "Actual")
Budget Amount:=Calculate([SumOfAmount];'DimScenario'[ScenarioName] = "Budget")

as @WimV suggested

像@WimV建议

1 个解决方案

#1

First calculated measure is good:

第一次计算结果良好:

SumOfAmount:=SUM('FactFinance'[Amount])

Add the following Calculated measures (if needed mark as hidden):

增加以下计算方法(如有需要，标记为隐藏):

Budget Amount:=Calculate([SumOfAmount],'DimScenario'[Scenariokey] = "Budget")
Actual Amount:=Calculate([SumOfAmount],'DimScenario'[Scenariokey] = "Actual")

You can use the new calculations for example in a difference calculations

你可以在差分计算中使用新的计算

#1