Search code examples
powerpivotdaxsubtotal

PowerPivot Rolling Total in Column

I have been asked to look into Response and Resolution times for incidents. Unfortunately the tool we have in place is not helpful in this area. Below is an extract of the data for 2 tickets:

Incident_Id | Status           |Begin_Time        |End_Time          | RowTotal
------------|------------------|------------------|------------------|--------
IM3415346   | Open             | 10/03/2017 11:20 | 10/03/2017 11:33 | 787
IM3415346   | Work In Progress | 10/03/2017 11:33 | 10/03/2017 11:55 | 1325
IM3415346   | Work In Progress | 10/03/2017 11:55 | 10/03/2017 13:20 | 5099
IM3415346   | Work In Progress | 10/03/2017 13:20 | 10/03/2017 13:56 | 2133
IM3415346   | Closed           | 10/03/2017 13:56 | 10/03/2017 13:56 | 0
IM3415483   | Open         | 10/03/2017 12:30 | 10/03/2017 12:39 | 530
IM3415483   | Work In Progress | 10/03/2017 12:39 | 10/03/2017 12:53 | 848
IM3415483   | Work In Progress | 10/03/2017 12:53 | 10/03/2017 14:10 | 4579
IM3415483   | Work In Progress | 10/03/2017 14:10 | 10/03/2017 14:30 | 1199
IM3415483   | Work In Progress | 10/03/2017 14:30 | 10/03/2017 16:55 | 8700
IM3415483   | Closed           | 10/03/2017 16:55 | 10/03/2017 16:55 | 0

The tool only says that a ticket is out of SLA and this is getting attributed to the team closing the ticket even if they were not the ones responsible for the delay.

Ideally I am trying to get a running total column for each incident next to the RowTotal:

RowTotal | RunningTotal |
---------|--------------|
787      | 786          |
1325     | 2111         |
5099     | 7210         |
2133     | 9343         |
0        | 9343         |
530      | 530          |
848      | 1377         |
4579     | 5956         |
1199     | 7155         |
8700     | 15855        |
0        | 15855        |

I have seen how to make cumulative total adding up all the times for each incident in turn...

=CALCULATE(SUM(Query[TotalSeconds]),Query[Incident_Id]=EARLIER(Query[Incident_Id]),Query[TotalSeconds]>0)

But I am really struggling with this running total approach. Is there anybody who might have come across this before?

Solution

  • I couldn't see TotalSeconds in the data provided so I'll just use RowTotal instead to illustrate. Feel free to change it to adopt to your exact use case.

    RunningTotal = 
CALCULATE(
    SUM(Query[RowTotal]),
    FILTER(
        Query,
        Query[Incident_Id] = EARLIER(Query[Incident_Id]) &&
        Query[Begin_Time] <= EARLIER(Query[Begin_Time])
    )
)

    You're missing the FILTER function for the row context to be working properly. You also need to add a filter on Begin_Time so it'll only add up the previous time logged, but not all rows in the incident.

    Depends on the business logic you may need to add filters on Status or also End_Time, but that's beyond the discussion.

    Result:

    result