时间戳

首页 > 解决方案 > Excel Power Pivot 通过多对一和一对多关系聚合数据

excel - Excel Power Pivot 通过多对一和一对多关系聚合数据

问题描述

我在 Power Pivot 中有 2 个大桌子，我正在尝试将库存构建等级与粉碎的库存等级相协调。请看例子。我可以创建包含粉碎等级的数据透视表，但我无法找到正确的方法来提供库存等级，尽管在附件示例中以绿色突出显示的对帐。

感谢您在哪里寻找任何帮助或方向我正在尝试的简化示例

标签： excelrelationshipone-to-manypowerpivot

解决方案

在 Power Query 中，创建查找表。

1) 独特的破碎机，ID 2) 日期，ID

如果需要，这是一个创建日期表的函数。在调用该函数以获取日期列后，为 ID 添加另一列。

/*--------------------------------------------------------------------------------------------------------------------
    PQ Create a Dates Table, returning a single column of dates.  

    Inputs:
    Start Date    | Enter the year as yyyy, month as mm, day as dd
    End Date      | Enter the year as yyyy, month as mm, day as dd
    Increments    | One row will be returned per increment. 

    Author:  Jenn Ratten

    Edits:   
    07/16/18      | Modified query copied from the internet.
    10/01/19      | Converted to a function.
--------------------------------------------------------------------------------------------------------------------*/

let

    fDatesTable = (StartYear as number, StartMonth as number, StartDay as number, EndYear as number, EndMonth as number, EndDay as number, IncrementDays as number, IncrementHours as number, IncrementMin as number, IncrementSec as number) as table =>

    let    

        StartDate = #date(StartYear,StartMonth,StartDay),
        EndDate = #date(EndYear,EndMonth,EndDay),
        Increments = #duration(IncrementDays,IncrementHours,IncrementMin,IncrementSec),
        DatesTable = Table.FromColumns({List.Dates(StartDate, Number.From(EndDate) - Number.From(StartDate), Increments)}, type table[Date]),
        ChangeType = Table.TransformColumnTypes(DatesTable,{{"Date", type date}})

    in

        ChangeType
in

    fDatesTable

将所有表加载到数据模型。

转到 Power Pivot、图表视图并创建您的关系。

  • Lookup Crusher 到数据表 1 和 2
  • 数据表 1 和 2 的查找日期

转到数据表 1 和 2 上的数据视图，为查找 ID 添加 2 个新列。您可以通过单击第一个单元格并使用此语法一次指定列标题和公式，然后按 Enter 或单击公式栏中的复选标记。

Dates Lookup ID:=RELATED(lookup_dates[ID])
Crusher Lookup ID:=RELATED(lookup_crusher[ID])

可选，但一个很好的做法.... 右键单击​​您刚刚创建的新字段并选择“从客户端工具中隐藏”。同时隐藏两个数据表上的日期和破碎机字段，以及两个查找表上的 ID 字段。当您创建数据透视表以汇总多个表中的数据时，放置在数据透视表上的文本字段应该是共享的字段（也称为查找表）。这有助于最大程度地减少总计与您在表格上实际看到的总和不匹配的枢轴。如果您隐藏这些字段，它会提醒您这一点。当然也有例外，但这是一个很好的经验法则。

现在创建度量来对吨和您想要的任何其他数学计算求和。使用这些措施，从简单开始，让枢轴进行切片。将度量值放在数据透视表的值部分。

Sum of Source Tons:=sum(Table1[Tons])

Sum of Destination Tons:=sum(Table2[Tons])

推荐阅读