Power Bi Desktop-如何在表之间添加值？ [英] Power Bi Desktop - How to add values between tables?

问题描述

我正在尝试创建一列，其值总计来自3个表的3列之间。 我将如何去做？

这两个表是共享ID的值表，它们都通过ID链接到帐户表。目标是加起来3列，并将其放入按ID分组的表中。

我尝试将它们加起来，尝试使用USERELATIONSHIP函数，然后在他们之间建立关系。它似乎给出了非常不准确的结果，好像将所有总数相加，然后将其传递给每个ID。那样，或者它不会让我使用该列，就好像它从未存在过一样。

编辑：关于我的概论尝试做（行应该指向帐户的ID列，但我弄乱了行）

编辑2 ：我也忘记了说明或提及。每个表中都有更多列的信息无法针对每个帐户进行汇总，这使我无法将表合并在一起。

解决方案

确保您的数据模型如下所示（根据您的喜好更改名称，但结构必须相同）：

在维建模中，表 Account是维，两个费用表都是事实表。合并共享相同维的多个事实表中的数据的操作称为跨钻，这是Power BI的标准功能。

要合并这些表中的费用，您只需使用度量，而不是列。本文介绍了区别：

结果：

当然，您不必将所有这些度量都放在矩阵中，我只是为了方便起见将它们显示出来，以验证结果。如果删除它们，最后的度量仍然有效：

I'm trying to create a column that has a total of values between 3 columns from 3 tables. How would I go about doing this?

The 2 tables are tables of values that share an id, and they are both linked to a table of account by Id. The goal is to add up 3 columns, and place it into a table grouped by the Id.

I've attempted summing them, trying to use the USERELATIONSHIP function, and creating a relationship between them. It seems to give very inaccurate results, as if it's summing all of the totals together, and passing them to each Id. That, or it won't let me use the column, as if it never existed.

EDIT: General Idea of what I'm trying to do (Lines should be pointing to Account's Id column, but I messed up the lines)

EDIT 2: I also forgot to illustrate or mention. There are more columns with information in each table that can't be summarized for each account preventing me from just merging the table together.

解决方案

Make sure your data model looks like this (change names as you please, but the structure must be the same):

In dimensional modeling, your table "Account" is a Dimension, and both fee tables are Fact tables. The operation of combining data from multiple fact tables that share the same dimension is called "drill-across", and it's a standard functionality of Power BI.

To combine fees from these tables, you just need to use measures, not columns. This article explains the difference:

Calculated Columns and Measures in DAX

First, create 2 measures for the fees:

Fee1 Amount = SUM(Fee_1[Amount])

Fee2 Amount = SUM(Fee_2[Amount])

Then, create a third measure to combine them:

Total Fee Amount = [Fee1 Amount] + [Fee2 Amount]

Create matrix visual, and place Account_ID from the Account table on the rows. Then drop all these measures into the matrix values area, like this:

Result:

Of course, you don't have to have all these measure in the matrix, I just showed them for your convenience, to validate the results. If you remove them, the last measure still works:

这篇关于Power Bi Desktop-如何在表之间添加值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！