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

[Anonymous]

Hi, 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 for 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
2	3000
3	5000

Can anyone help me?

Thanks!

[Vera_33]

Hi @Anonymous , can you try it? Replace the Table1 with your own table name.

Measure =

VAR T1 = GROUPBY(Table1,Table1[House_Id],"MaxV",MAXX(CURRENTGROUP(),[Version_Id]))

RETURN

CALCULATE(SUM('Table1'[Value]),TREATAS(T1,'Table1'[House_Id],Table1[Version_Id]))

[HotChilli]

Something like this measure:

MSum = VAR _maxVer = CALCULATE(MAX(TableR[Version_Id]), ALLEXCEPT(TableR, TableR[House_Id]))
RETURN
    CALCULATE(SUM(TableR[Value]), TableR[Version_Id] = _maxVer)

Please test with a larger sample dataset

[Anonymous]

No,

This would return just 2000

[amitchandak]

@Anonymous , Create a measure like this and ad

Measure =
VAR __id = MAX ( 'Table'[House_Id] )
VAR __Version_Id = CALCULATE ( MAX( 'Table'[Version_Id] ), ALLSELECTED ( 'Table' ), 'Table'[House_Id] = __id )
RETURN CALCULATE ( sum ( 'Table'[Value] ), VALUES ( 'Table'[House_Id ), 'Table'[House_Id] = __id, 'Table'[Version_Id] = __Version_Id )

[Anonymous]

@amitchandak That didn't work

@Vera_33 That also didn't work

[camargos88]

Hi @Anonymous ,

Try this measure:

MaxValue =

SUMX(ADDCOLUMNS(SUMMARIZE('Table'; 'Table'[House_Id]; "MaxValue"; MAX('Table'[Version_Id])); "Value";

CALCULATE(SUM('Table'[Value]); FILTER(ALL('Table'); 'Table'[House_Id] = EARLIER('Table'[House_Id]) && 'Table'[Version_Id] = [MaxValue]))); [Value])

Ricardo

[Anonymous]

Didn't work @camargos88 

[parry2k]

@Anonymous this will do it

Measure = 
SUMX ( 
    VALUES ( 'Table'[House_Id] ), 
    CALCULATE ( 
        MAX ( 'Table'[Value] ), 
        ALLEXCEPT ( 'Table', 'Table'[House_Id] ) 
    )
)

I would 💖 Kudos 🙂 if my solution helped. 

[Anonymous]

@parry2k nope, this measure does not even take into account eht Version_Id

[parry2k]

@Anonymous ah, I missed that, I thought you want the highest value of the house ignoring version, but I think you are looking the value of the highest version of the house. 

[parry2k]

@Anonymous here it is, add the following two measures and measure 2  will do the job

Measure = 
CALCULATE ( 
    SUM ( 'Table'[Value] ),
    FILTER ( 
        ALLEXCEPT ( 'Table', 'Table'[House_Id] ), 
        'Table'[Version_Id] = MAX ( 'Table'[Version_Id]  )
    )
)


Measure 2 = SUMX ( VALUES ( 'Table'[House_Id] ), [Measure] )

I would 💖Kudos 🙂 if my solution helped. 

[Anonymous]

@parry2k  This doesn't work also. The think is that the measure should Summarize by House_Id on the Max(Version_Id), then do an INNER JOIN with the main table. Then sum on this virtual table.

But I don't know how to do this with dax.

[parry2k]

@Anonymous It Seems like I'm not seeing all the details. See the attached solution and it works, you can take it from there or provide what I'm missing.

[Anonymous]

ok @parry2k It works on your version, but in reallity I have more variables, such as Color_Id and more.

Therefore If I want to sum by color_Id insted of House_Id It does not work.

See https://we.tl/t-GaIKGSNEDP 

[parry2k]

@Anonymous so you by the colour id of the same house or colour id across all the houses?

[Anonymous]

Color Id accross all house but only taking into account where version is max on each house.

[Vera_33]

Hey, you never mentioned you have color id. You need to modify the measure accordingly. I got it in your shared file.

Measure 3 =
VAR T1= GROUPBY('Table','Table'[House_Id],'Table'[Color_Id],"MaxV",MAXX(CURRENTGROUP(),[Version_Id]))
RETURN
CALCULATE(SUM('Table'[Value]),TREATAS(T1,'Table'[House_Id],'Table'[Color_Id],'Table'[Version_Id]))

[Anonymous · Accepted solution]

The only answer that works, and even with other columns (like Color_id, or more) is this one answered in SO:

https://stackoverflow.com/questions/61390142/sum-where-version-is-highest-by-another-variable-no-max...

[parry2k]

@Anonymous so what makes you think it is not working by color, I think it is correct until I'm missing something, can you tell me the expected output by color.

[Anonymous]

@parry2k That's correct I want to sum the values for all the houses but only where the version id is the max on each house.