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

问题描述

我正在尝试创建一个列，该列在 3 个表中的 3 列之间具有总值.我该怎么做?

这 2 个表是共享一个 id 的值表，它们都通过 Id 链接到一个帐户表.目标是将 3 列相加，并将其放入按 Id 分组的表中.

我尝试对它们求和，尝试使用 USERELATIONSHIP 函数，并在它们之间创建关系.它似乎给出了非常不准确的结果，就好像它将所有总数加在一起，并将它们传递给每个 Id.那样，否则它不会让我使用该列，就好像它从未存在过一样.

编辑:我正在尝试做的一般概念(行应该指向 Account 的 Id 列，但我搞砸了行)

编辑 2:我也忘了说明或提及.每个表格中有更多列包含无法针对每个帐户汇总的信息，这使我无法将表格合并在一起.

解决方案

确保您的数据模型看起来像这样(随意更改名称，但结构必须相同):

在维度建模中，您的帐户"表是一个维度，两个费用表都是事实表.组合来自共享相同维度的多个事实表的数据的操作称为钻取"，它是 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屋！