Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
thomas_pike
Frequent Visitor

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

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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

1.png2.png

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]

  

View solution in original post

2 REPLIES 2
tamerj1
Super User
Super User

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

1.png2.png

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]

  

Thanks @tamerj1 that got me on the right path.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.