另一个变量最高版本的总和(整个数据中没有最大版本) [英] Sum where version is highest by another variable (no max version in the whole data)

问题描述

我很难让这个措施发挥作用.

I'm struggling having this measure to work.

我想要一个衡量标准，仅对每栋房屋的最大版本的价值求和.

I would like to have a measure that will sum the Value only for the max version of each house.

所以按照这个示例表:

|---------------------|------------------|------------------|
|      House_Id       |     Version_Id   |     Value        |
|---------------------|------------------|------------------|
|          1          |         1        |       1000       |
|---------------------|------------------|------------------|
|          1          |         2        |       2000       |
|---------------------|------------------|------------------|
|          2          |         1        |       3000       |
|---------------------|------------------|------------------|
|          3          |         1        |       5000       |
|---------------------|------------------|------------------|

此测量的结果应该是:10.000，因为 house_id 1 版本 1 被忽略，因为还有另一个更高的版本.

The result of this measure should be: 10.000 because the house_id 1 version 1 is ignored as there's another version higher.

通过 House_id，结果应该是:

By House_id the result should be:

|---------------------|------------------|
|      House_Id       |     Value        |
|---------------------|------------------|
|          1          |       2000       |
|---------------------|------------------|
|          1          |       3000       |
|---------------------|------------------|
|          2          |       5000       |
|---------------------|------------------|

谁能帮帮我?

鉴于@RADO 给出的正确答案，现在我想进一步加强这一措施:

Given the correct answer @RADO gave, now I want to further enhance this measure:

现在，我的主数据表实际上有更多列.如果我想将此度量添加到表格视觉对象中，该图表将度量按来自数据表(或与之相关)的另一列拆分.

Now, my main Data table in reality has more columns. What if I want to add this measure to a table visual that splits the measure by another column from (or related to) the Data table.

例如(简化数据表):

|---------------------|------------------|------------------|------------------|
|      House_Id       |     Version_Id   |     Color_Id     |       Value      |
|---------------------|------------------|------------------|------------------|
|          1          |         1        |    1 (Green)     |       1000       |
|---------------------|------------------|------------------|------------------|
|          1          |         2        |    2 (Red)       |       2000       |
|---------------------|------------------|------------------|------------------|
|          2          |         1        |    1 (Green)     |       3000       |
|---------------------|------------------|------------------|------------------|
|          3          |         1        |    1 (Green)     |       5000       |
|---------------------|------------------|------------------|------------------|

主表中有一个 Color_Id 连接到一个 Color 表.然后我添加一个带有 ColorName(来自 ColorTable)和度量值的可视表(ColorId 1 是绿色，2 是红色).

There's a Color_Id in the main table that is connected to a Color table. Then I add a visual table with ColorName (from the ColorTable) and the measure (ColorId 1 is Green, 2 is Red).

对于给定的答案，当按 ColorName 过滤时结果是错误的.虽然 Total 行确实是正确的:

With the given answer the result is wrong when filtered by ColorName. Although the Total row is indeed correct:

|---------------------|------------------|
|      ColorName      |      Value       |
|---------------------|------------------|
|        Green        |       9000       |
|---------------------|------------------|
|        Red          |       2000       |
|---------------------|------------------|
|        Total        |       10000      |
|---------------------|------------------|

这个结果是错误的，因为 9000 + 2000 是 11000 而不是 10000.该度量应忽略具有旧版本的行.在前面的示例中，House_Id 1 和 Color_Id Green 所在的行是旧版本(该 House_Id 有新版本).

This result is wrong per ColorName as 9000 + 2000 is 11000 and not 10000. The measure should ignore the rows with an old version. In the example before this is the row for House_Id 1 and Color_Id Green because the version is old (there's a newer version for that House_Id).

所以:

1. 我该如何解决这种情况?
2. 如果我想按数据表中的(或与之相关的)另一列(例如 Location_Id)进行过滤，该怎么办?是否可以以适用于主数据表中列的任何给定数量拆分的方式定义度量?

推荐答案

我使用Data"作为您的表的名称.

I use "Data" as a name of your table.

Sum of Latest Values =
VAR Latest_Versions =
    SUMMARIZE ( Data, Data[House_id], "Latest_Version", MAX ( Data[Version_Id] ) )

VAR Latest_Values =
    TREATAS ( Latest_Versions, Data[House_id], Data[Version_Id] )

VAR Result =
    CALCULATE ( SUM ( Data[Value] ), Latest_Values )

RETURN Result

测量输出:

它是如何工作的:

1. 我们计算 house_ids 及其最大版本的虚拟表，并将其存储在变量Latest_Versions"中
2. 我们使用第一步中的表格来过滤最新版本的数据，并建立适当的数据沿袭(https://www.sqlbi.com/articles/understanding-data-lineage-in-dax/)
3. 我们通过仅过滤最新值的数据来计算最新值的总和.

您可以在此处了解有关此模式的更多信息:https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/

You can learn more about this pattern here: https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/

这篇关于另一个变量最高版本的总和(整个数据中没有最大版本)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！