Capping a cumulative total

[Daviejoe]

Hi All,

I have a machinery table, where downtime has a cost per minute.  So, as per below, Event 1 the machinery was down for 100 minutes at a per minute rate of £65.00.  These are all in my table.  The total cost is not in my table.  The total cost of a single event will get capped at £50,000.

I'm going to have a lot of rows of data in this table so thinking that it would be best to have the calculation as a measure and not as a calculated column.

How do i get the calculation to give me my individual event cost, capped at £50,000, and give me my total cost of all events but still capping those events at £50,000?

In my table	In my table	In my table		Not in my table
Event No	No of Mins	Rate per minute		Total Cost
Event 1	100	£                       65.00		£      6,500.00
Event 2	1890	£                       65.00		£ 122,850.00
Event 3	1000	£                       65.00		£    65,000.00
Event 4	899	£                       65.00		£    58,435.00
Event 5	456	£                       65.00		£    29,640.00
Event 6	1059	£                       65.00		£    68,835.00
Event 7	1560	£                       65.00		£ 101,400.00

[amitchandak · Accepted solution]

@Daviejoe, if the rate is the same for everyone

New Measurement: Sum(Table[No Minimum]) * max(Table[Rate])

if you don't

New measurement: sumX(Table, Table[Mins Not]*Table[Rate])

[Daviejoe]

Thanks @amitchandak 

Each event will need to be capped at £50,000 so I need do that at a row level and also when I roll up all the events into one KPI.