Forum Discussion
Subtract Time Entries
A measure would be best practice (https://docs.microsoft.com/en-us/dax/average-function-dax)
However, you may look to just use the standard aggregation facility.
https://docs.microsoft.com/en-us/power-bi/create-reports/service-aggregates
Create a bar chart.
x = Responder Type
y = response time
click on the values well and change the value from sum to average.
If I have answered your question please mark my post as the solutiuon. If any of my posts helped please 'like' them. Appreciate the Kudos.
Thanks
Is it working now?
I have screen shots to show you but let me see if I can just post the text first.
1. merge the event and responder columns to create a unique ID
2. pivot the action putting it into columns. Ensure that under advanced the response time is not aggregated.
that will get your times into columns by action.
3. Select the columns you want to subtract. On ribbon under add column, click on time (the clock) and subtract. That should give you the difference value.
note if one of the times isn't there then it doesn't return a result. Which is what I think you want.
4. close and load into power bi.
5. Build dax measure for average... you may even be able to get away with just putting the difference value into the visual and then changing to an average in the fields well. Not best practice but if gives you what you need then tick.
Let me know if you need the screenshots.
Yeah, could you provide screen shots. I'm sorry, I feel bad. I'm having trouble finding how to merge the Event Id and Responder Id column.
Ok will try again now. But here is how to merge columns.
https://support.microsoft.com/en-us/office/merge-columns-power-query-80ec9e1e-1eb6-4048-b500-d5d42d9f0a8dNote - I used an online image scrape of one the images you posted to get the data. Not all came through accurate so I removed those columns.
1. the merged column (please see article on how to accomplish above)
2. pivot column (https://support.microsoft.com/en-us/office/pivot-columns-abc9c8da-3be9-44c4-886e-0be331ab387a)
3. subtract to get time difference
4. assuming you know how to load5. that might be another post to create the measure.
hope that helps