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:   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 ...
  • 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]

      

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]

  

thomas_pike's avatar
thomas_pike
Frequent Visitor
4 years ago

Thanks tamerj1 that got me on the right path.