Calculate the sum of the line amounts with the highest version number

[KLJ]

Hi

I need to calculate the sum af the line amount but only on the rows where the version numner is the highest.

for order# 1 calculate the sum of line amount where version# is 2

for order# 2 calculate the sum of line amount where version# is 4

and so on.

The Version no is not static.It can change when I update the data.

Hope someone can help

My data look like this (very simplefied)

Order NO	Version NO	Item NO	Line amount
1	1	10	5,00
1	1	11	9,00
1	1	12	7,00
1	2	10	5,00
1	2	11	9,00
1	2	12	7,00
2	1	25	7,00
2	1	37	4,00
2	1	44	8,00
2	1	99	4,00
2	2	25	7,00
2	2	37	4,00
2	2	44	8,00
2	2	99	4,00
2	3	25	7,00
2	3	37	4,00
2	3	44	8,00
2	3	99	4,00
2	4	25	7,00
2	4	37	4,00
2	4	44	8,00
2	4	99	4,00
3	1	66	9,00

[LarsSchreiber · Accepted solution]

Hi @KLJ,

try this:

MSumMaxVersionNO :=
CALCULATE (
    SUM ( [Line amount] );
    FILTER ( Tabelle1; Tabelle1[Version NO] = MAX ( Tabelle1[Version NO] ) )
)

Regards,

Lars

[konstantinos]

- edited - This is not working correct I will reply later with the solution I proposed

@KLJ  If you need to use it without putting Orders in graph you can wrap  @LarsSchreiber  formula in a SUMX .

MSumMaxVersionNO  2 :=

VAR maxversion =
    CALCULATE (
        SUM ( Table1[Line amount] );
        FILTER ( Table1; Table1[Version NO] = MAX ( Table1[Version NO] ) )
    )
RETURN
    SUMX ( VALUES ( Table1[Order NO] ); maxversion )

[KLJ]

Hi Lars

Thank you. that did it