If then statement as calculated column and in cross table

[Kinimod24]

Hello, 

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 time*hour rate)>total milestones, total milestones, (actual time*hour 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 

 

[David Boot-Olazabal]

Hi,

You can add the expression in the cross table as you mentioned. And you can also use the OVER functions to achieve this in a calculated column.

Consider the below screenshot, where I have copied a small part of your dataset. I have used the OVER function in the crosstab columns of Total Milestones and Actual Time to calculate the Revenue per Project:

The expression used is this one:
<[Data Table].[Project] NEST Sum([Data Table].[Total Milestones]) OVER ([Project]) NEST [Data Table].[Hour rate] NEST Sum([Data Table].[Actual Time]) OVER ([Project])>.

The Revenue column is a calculated column, based upon your formula.

Kind regards,

David