Sum where version is highest by another variable (no max version in the whole data)

Question

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       |
|---------------------|------------------|------------------|

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

By House_id the result should be:

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

Can anyone help me?

EDIT:

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       |
|---------------------|------------------|------------------|------------------|

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).

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      |
|---------------------|------------------|

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).

So:

How can I address this situation?
What If I want to filter by another column from (or related to) the Data table such as Location_Id? It is posible to define the measure in such a way that could work for any given number splits for columns in the main Data table?

Related: https://stackoverflow.com/questions/52525377/return-top-value-ordered-by-another-column — Alexis Olson, Apr 23 '20 at 18:11
@AlexisOlson This link refers to the TOP row, but I want the row where version is the maximum version on a group. — janscas, Apr 28 '20 at 08:44
You just need to filter the table you're applying `TOPN` to then. — Alexis Olson, Apr 28 '20 at 13:21
But you do need to sum as well as another step, so this isn't a duplicate. — Alexis Olson, Apr 28 '20 at 13:26

score 2 · Accepted Answer · answered Apr 23 '20 at 16:14

2

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

Measure output:

How it works:

We calculate a virtual table of house_ids and their max versions, and store it in a variable "Latest_Versions"
We use the table from the first step to filter data for the latest versions only, and establish proper data lineage (https://www.sqlbi.com/articles/understanding-data-lineage-in-dax/)
We calculate the sum of latest values by filtering data for the latest values only.

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

answered Apr 23 '20 at 16:14

RADO

7,733
3
19
33

now I want to add another column, like Color_Id and separate the latest values by this column in a table. It appears that the total row is ok (only shows the latest value), but each color_id shows the sum of all the versions (not just the latest). – janscas Apr 27 '20 at 14:57
Where is Color_id column coming from? The same table? – RADO Apr 27 '20 at 22:30
Yes, same table, but I have the color name in a related table joined by color id – janscas Apr 28 '20 at 08:03
@janscas - conceptually, you need to add color id to the first two variables, the same way house_id was used. If you need more help to figure this out, it'll be easier to help you if you post it as a new question with a new data sample and desired result. – RADO Apr 28 '20 at 16:44
ok @RADO, but if I add the Color_Id to the first two variables then the max version will work per House & color and that's not what I want. The max is per House only, but allow me to crossfilter by Color. – janscas Apr 28 '20 at 17:31
Hey, can you help me out? I feel the answer is simple but i'm unable to achieve it – janscas May 04 '20 at 08:38
I'll be happy to help, but I don't really understand what you are trying to do and what the problem is. It's hard to solve issues like this blindly, without seeing the data /data model and the expected results to test against. Update your question or post a new one with the additional info, and I'll look at it – RADO May 04 '20 at 17:50

Sum where version is highest by another variable (no max version in the whole data)

1 Answers1

Linked