Calculation using Multiple Values for Maximum Row

[thomas_pike]

Good Afternoon,

I am trying to create the following table using DAX:

ID	Index	Value	Pitch	Result
1	1	2	2	2
1	2	4	2	4
1	3	6	2	6
2	1	5	3	3
2	2	7	3	6
2	3	9	3	9

Result is a calculated column with the following formula when grouped by ID:

Pitch = Value / Index <- for the row with the max value per ID.

Result = Index * Pitch

What is the best way to calculate the Pitch column?

Thank you for your assistance

[tamerj1 · Accepted solution]

Hi @thomas_pike 
Please do the following https://we.tl/t-JYIy6UCpSM

Pitch = 
VAR CurrentIDTable = 
    CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[ID] ) )
VAR LastIndexRecord =
    TOPN ( 1, CurrentIDTable, Data[Index] )
VAR LastIndex = 
    MAXX ( LastIndexRecord, Data[Index] )
VAR LastValue =
    MAXX ( LastIndexRecord, Data[Value] )
RETURN
    LastValue / LastIndex

Result = Data[Index] * Data[Pitch]

[thomas_pike]

Thanks @tamerj1 that got me on the right path.