I have a data table that has a lot of data (multiple lines with same project) that flows into a cross table. I am missing my last piece : revenue (highlighted in blue). it is an “if, then” statement. The goal is to have this as a calculated column so that I can use this for other calculations

I would like a calculated column that does: =If (actual timehour rate)>total milestones, total milestones, (actual timehour rate))

problem is that if i do it in the data as a calculated column it looks at each row individually. Instead each project with sum of actual time, sum of all milestone, and max of hourly rate etc

I can probably do it directly in the cross table as an expression instead of calculated column but I would think that is not my only option.

Couple things to consider: all the columns in Data are Calculated columns.

Active Milestone + Complete Milestones = Total Milestones.

Total Milestones/Planned work =Hourly rate.

actual time * hourly rate = revenue

I created a calculated column but does not work