Hi all,
I’m desperately in need of help before I pull my hair out.
I want to calculate the percentage of our Quotes for previous months (by month) hit the 1020 minute SLA.
Dataset has, amongst others, the following columns:
QuoteID, Stage (there are 4 stages), Stage Start (date and time), Stage End (date and time)
Trouble is, there’s a new row for every time the QuoteID starts a new stage.
So I originally calculated these columns, thinking I was on to a winner:
Stage 1 Duration (if stage = 1, diffinhours between the start and end date), Stage 2 Duration (if stage = 2, diffinhours etc as above), stage 3 duration, stage 4 duration, Customer Service Minutes (sum of stage 1 and 2), sales minutes (sum of stage 3 and 4) and then Total (sum of 1+2+3+4).
However, I essentially need to replicate what a pivot table will do because running that calculation in the total column will calculate a total for each row, when really I want to total the minutes PER QUOTEID.
So that when I do a count of how many were equal to or less than the SLA of 1020 minutes, it only counts each QuoteID once and sums the minutes.
HOW do I do this?!
Can send table if needed but cannot share pbix unfortunately.