总和,其中版本最高的是另一个变量(整个数据中没有最高版本) [英] 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.
因此,请参见以下示例表:
So following this example table:
|---------------------|------------------|------------------|
| 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.
例如(简化的数据表):
For example (simplified 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过滤时,结果是错误的。虽然总计行确实是正确的:
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 |
|---------------------|------------------|
每个ColorName的结果都是错误的,因为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).
所以:
- 如何解决这种情况?
- 如果我想按(或与)数据表(例如Location_Id)?可以这样定义度量,使其适用于主数据表中列的任何给定数字拆分吗?
推荐答案
我使用数据作为表的名称。
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
测量输出:
工作原理:
- 我们计算house_ids及其最大版本的虚拟表,并将其存储在变量 Latest_Versions中
- 我们使用第一步中的表来仅过滤最新版本的数据,并建立适当的数据沿袭
( https://www.sqlbi.com/articles/understanding-data-lineage-in-dax/ ) - 我们通过过滤来计算最新值的总和
您可以在此处了解有关此模式的更多信息:
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屋!