Forum Discussion

thomas_pike's avatar
thomas_pike
Frequent Visitor
4 years ago
Solved

Calculation using Multiple Values for Maximum Row

Good Afternoon,

 

I am trying to create the following table using DAX:

 

IDIndexValuePitchResult
11222
12424
13626
21533
22736
23939

 

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's avatar
    tamerj1
    4 years ago

    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]

      

2 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    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]