excel - Powerpivot - 从单个事实表创建比率

Question

我有一个看起来像这样的 powerpivot 表......所有“事实”都在一个表中，其中 Information 列用于隔离:

Information | Year | Amount
Expense       2010   1000000
Units Sold    2010   50000
Expense       2011   2000000
Units Sold    2011   125000

我希望能够计算各种比率。这似乎是一个简单的需求，但我无法弄清楚或在线找到解决方案。

例如...

2010 年、2011 年或组合销售的费用/单位(基于 Excel 中应用的过滤器)

..... 2010 = 1000000/50000 = 20； 2011 = 2000000/125000 = 16；所有时间 = 3000000/175000 = 17.14
等等。

实际上，还有更多维度(区域、位置、月份等)和其他信息类型(人数、平方英尺等)，但只有三四个分母，所以我希望解决方案能够扩展。

如何在 Powerpivot 中做到这一点？我可以使用(例如)CALCULATE(SUM(fact[amount]),fact[information]="Units Sold")) 计算分母，但无法将该分母应用于所有分子。

X
X
X
X

根据下面提供的答案添加跟进...

问题 #1:
当我查看总费用时，下面提供的解决方案有效，但实际上，费用行将是多行(工资、福利、租金等)，我需要能够计算每个销售单位。该解决方案似乎不允许这样做。

问题 #2:
事实表目前有四种不同的信息类型……费用(如上文所述)、销售单位、员 worker 数和平方英尺。我希望能够做的是计算每售出费用、每单位售出人数等。换句话说，一个分母可以应用于所有行，然后可以很容易地在数据透视表中求和。该解决方案将所有不是已售出的单位，对其求和，然后除以已售出的单位...混合费用、人数和平方英尺。

再次感谢您的帮助。

Answer 1

SumAmt:= SUM( FactTable[Amount] )

UnitsSoldDenominator:=
DIVIDE(
    CALCULATE(
        [SumAmt]
        ,FILTER(
            VALUES( FactTable[Information] )
            ,FactTable[Information] <> "Units Sold"
        )
    )
    ,CALCULATE(
        [SumAmt]
        ,FactTable[Information] = "Units Sold"
    )
)

这将给出你想要的行为。

值得花一些时间来解释为什么我们对 CALCULATE() 的过滤器参数在分子和分母上是不同的。

带有文字谓词的 CALCULATE() ，就像我们在 DIVIDE() 的第二个参数中看到的那样，被隐式重写为以下内容:

CALCULATE(
    [SumAmt]
    ,FILTER(
        ALL( FactTable[Information] )
        ,FactTable[Information] = "Units Sold"
    )
)

FILTER() 简单地遍历在参数 1 中传递给它的表，并从该表中返回参数 2 返回 true 的行。

有了隐式的 ALL() ，就没有从数据透视表中保留上下文。

VALUES() 评估过滤器上下文中的表或列引用。因此，当我们评估 VALUES(FactTable[Information]) 时，我们只返回数据透视表中上下文中的值。在任何给定的年份，我们都会取回当年存在的每个标签，总计我们会得到每个标签。在“已售出的单位”级别，我们什么也得不到，因为我们正在过滤掉它。

这是一个数据透视表的图像，其中包含基于您的示例数据和此度量的预期行为。



编辑维度表

UnitsSoldDenominator:=DIVIDE(
    CALCULATE(
        [SumAmt]
        ,FILTER(
            VALUES( DimInformation[Information] )
            ,DimInformation[Information] <> "Units Sold"
        )
    )
    ,CALCULATE(
        [SumAmt]
        ,DimInformation[Information] = "Units Sold"
    )
)

这取决于在 FactTable 和 DimInformation 之间定义的事件关系。我已经在数字 [InformationKey] 上定义了我的。

下图包括基于您在原始问题中提供的内容的示例数据，以及从中推断出的模拟维度。数据透视表与展开的字段列表一起显示。我的模型中的关系显示在 Power Pivot 窗口的管理关系对话框中。我看到的行为与编写针对 FactTable[Information] 提供的原始度量值与针对 DimInformation[Information] 编写的新版本相同。

如果您的模型配置相同并且您无法重现，请告诉我。



为层次结构编辑 2

UnitsSoldDenominator:=DIVIDE(
    CALCULATE(
        [SumAmt]
        ,FILTER(
            DimInformation
            ,DimInformation[Information] <> "Units Sold"
        )
    )
    ,CALCULATE(
        [SumAmt]
        ,ALL( DimInformation )
        ,DimInformation[Information] = "Units Sold"
    )
)

在这里，我们在分母中添加了对 ALL( DimInformation ) 的调用。这是因为我们将在来自 DimInformation[InformationGroup] 和 DimInformation[Information] 的数据透视表中拥有过滤器上下文。 CALCULATE() 中的过滤谓词 DimInformation[Information] = "Units Sold"隐式剥离了存在于 DimInformation[Information] 上的过滤上下文，但是当我们将 DimInformation[InformationGroup] 放入数据透视表时，它提供了一组新的过滤器上下文(所有过滤器上下文都在逻辑与中评估)。

因此，我们正在寻找(在下图中的示例中)DimInformation[InformationGroup] = "Expense"(这是数据透视表提供的上下文)&& DimInformation[Information] = "Units Sold"。这在我们的数据中是不可能的，所以分母是空白的。

因此，我们从所有 DimInformation 中剥离过滤器上下文，然后评估我们的文字谓词。