Forum Discussion
Capping a cumulative total
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 |
@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])
2 Replies
@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])
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.
