PowerPivot的摘要 [英] Sumif for PowerPivot

问题描述

我对使用PowerPivot和DAX有点挣扎还很陌生-帮助！

I'm fairly new to using PowerPivot and struggling with DAX a little - help!

我有两个表： Values 和办公室。

值有，以及其他列，其中包括 OFFICE ， TARGET 和 ACTUAL 。办事处有 OFFICE ， REGION ， MANAGER 和 PROVIDER 。

Values has, among other columns, OFFICE, TARGET and ACTUAL. Offices has OFFICE, REGION, MANAGER and PROVIDER.

我想计算 REGION 的实际值， code> MANAGER 和 PROVIDER 在值表上，因此，当用户选择一个办公室或一系列办公室时，他们会看到其值选择，然后针对所选区域或所选经理的所有其他办公室。在Excel中执行此操作，我只会使用：

I want to calculate the actual for REGION, MANAGER and PROVIDER on the Values table so when the user selects an office or a range of offices they see the values for their selection and then against all other offices in the selected regions or for the selected managers. Doing this in Excel I would just use:

=SUMIF(OFFICES[REGION],OFFICES[@REGION],[ACTUAL])

我尝试使用calculate函数，但无法正常工作。

I've tried to use the calculate function but can't get it to work.

推荐答案

为此，您想利用PowerPivot强大的基于关系的计算引擎。 PowerPivot是一台PivotTable生成机器，不仅是一个电子表格，它可以容纳比Excel多的行，或者使用SQL（与我最初的看法相反）。

For this, you want to make use of PowerPivot's powerful relation-based calculation engine. PowerPivot is a PivotTable generating machine, not just a spreadsheet that can hold more rows than Excel or that uses SQL (contrary to my initial belief).

您的OFFICES表是我们所谓的维度或查找表（取决于您与谁交谈），而VALUES是事实或数据表。正如我看到的那样，我们的事实表包含所有真实数据，将其命名为VALUES，维度描述了数据，并允许我们对&骰子。这两种类型的表通过关系进行交互。

Your OFFICES table is what we call a "dimension" or "lookup table" (depending on who you talk to) and VALUES is a "fact" or "data" table. Our "fact" tables contain all the real data as I see you noticed by naming it VALUES and the "dimensions" describe the data and allow us to slice & dice by it. These two types of tables interact via "relationships."

在Excel 2013中，我发现图视图是创建关系的最简单方法。对于此方法，请转到PowerPivot的主页选项卡，然后单击图表视图。这将显示数据模型中的所有表以及它们各自的字段（列）。单击并按住值选项卡上的办公室字段，然后将其拖到办公室的办公室字段。

In Excel 2013, I find the diagram view the easiest way to create a "relationship." For this method, go to the Home tab of PowerPivot and click Diagram View. This will show all of the tables in your data model with all of their respective fields (columns). Click and hold on the Office field on the VALUES tab and drag it to the Office field of OFFICE.

这表示在多对一关系中，VALUES是很多方面，而OFFICE是很多方面。例如，如果给定的Office有多个提供者，则将导致该Office在OFFICE维度中出现多次，从而导致关系问题。在这种情况下，您可能需要考虑重新构造查询以促进事实到维度的多对一结构（即，将提供程序移动到VALUES表）。

This indicates that VALUES is the "many side" and OFFICE is the "one side" in a "many-to-one" relationship. If, for example, a given Office has multiple Providers, that would cause that Office to appear more than once in the OFFICE dimension, causing a problem for the relationship. If that is the case, you might want to consider re-structuring your queries to facilitate fact-to-dimension many-to-one structure (i.e. - moving provider to the VALUES table).

创建此关系后，转到值选项卡，然后在窗口底部（位于所有数据下方）的计算区域中，输入以下代码以创建度量。

Once this relation is created, go to the VALUES tab and in the calculation area at the bottom of your window (below all of your data) enter the following code to create a "measure."

sumActual:=SUM(Values[Actual])

创建此度量后，单击PowerPivot窗口主页选项卡上的数据透视表，以在Excel的前端中创建数据透视表。在这里，您将能够按区域，管理者和/或提供者筛选，划分或透视您的实际值，并快速重新布置透视图，而无需任何新的计算或结构工作！

With this "measure" created, click PivotTable on the Home tab of your PowerPivot window to create a PivotTable in the "front-end" of Excel). Here, you will be able to filter, slice, or pivot your Actuals by Regions, Managers and/or Providers and quickly rearrange your pivot without any new calculation or structural work!

还可以为目标甚至方差/％方差创建度量。

Feel free to also create "measures" for Targets or even variance/%variance.

sumTarget:=SUM(Values[Target])
variance:=sumActual-sumTarget
%variance:=DIVIDE(variance,sumTarget,BLANK())

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