TOTAL
TOTAL(amount over time)
The whole of an amount over time, as a plain number: all the hours a spread holds, or how much falls in a window when the amount has been CLIPped. Only an amount has a total; a level, such as % complete over time, holds a value rather than adding one up, so read it on a date with AT instead.
Arguments
| Argument | What to give it |
|---|---|
| amount over time | An amount: from SPREAD, CHANGE, CLIP of one, or a SUM of them. A list of amounts clipped to a period set's dates gives a list on the periods, one total per period. |
Examples
Formula=TOTAL(CLIP(SPREAD(INHOURS([Duration]), [Start date], [Finish date]), [Start date], DATADATE()))
The hours the task was planned to have worked by the data date.
Formula=TOTAL(CHANGE(HISTORY([% complete])))
All the progress recorded across the updates, added up.
Measure (on the project)=TOTAL(CLIP(SUM(SPREAD(INHOURS(Tasks[Duration]), Tasks[Start date], Tasks[Finish date])), DATADATE(), ADDWORK(DATADATE(), 10d)))
As a measure: the hours planned across the project in the next ten working days.
Measure (on the project)=LET(w, PERIODS("week", [Start date], [Finish date]), MAX(TOTAL(CLIP(SUM(SPREAD(INHOURS(Tasks[Duration]), Tasks[Start date], Tasks[Finish date])), w[Start date], w[Finish date]))))
The busiest week's hours: one total per week, then the largest.
Reach for something else when
| You want | Use |
|---|---|
| The running total up to a date | AT |
| A level's value on a date, or its peak | AT, MAX |
| Several rows' amounts added together, still over time | SUM |
Common questions
Why does TOTAL refuse HISTORY([% complete])?
A level holds a value from one update to the next; adding it up over time means nothing. The editor says: "TOTAL adds up an amount over time; a level has no total. Use AT(…, date) for its value on a day".
The editor refuses this=TOTAL(HISTORY([% complete]))
What does the grid show for a column that is over time?
An amount shows its total, the same number TOTAL gives; a level shows its latest value. TOTAL is for when you need the number inside a formula: to compare, divide or add it to something else.