If I understand your question correctly, the following approach may help.
1. Change the data type of Duration to Number (Decimal) (right-click on the field, Change Data Type).
2. Convert Duration to Measure (right-click on the field, Convert To Measure).
3, Make sure Default Properties - Aggregation of the field is set to Sum.
4. Drag Reason Code to Columns, Agent ID to Rows, Duration to Text.
5. Right-click on Duration in the Marks card, choose Quick Table Calculation, choose Percent of Total.
I'm attaching the workbook I produced. Hope this helps - let me know if you have more questions.
DurationPercentOfTotal.twbx 25.0 KB
Alternatively, you can create a calculated field called DurationInt as DATEDIFF('second', #1900/1/1#, [DurationDT]), and drag this to Sum and use in the Table calculation instead of the converted Duration.
Thank you Stoyko, much appreciated your answer. But I am having difficulty when changing to numbers. The total hours of work do not make any sense to me.Here is the original screenshot
After converting to numbers, the hours get converted into some decimal numbers and total hour shows as 0.17 instead of 4 hours and 10 minutes.
I was not able to open worksheet because I have older Tableau 9.1 version. Thanks again
Yes. The displayed numbers themselves don't make much sense, but the percentages are correct. I assumed this was all you wanted to see.
To get the actual totals, you need another calculated field.
First, define DurationInt as DATEDIFF('second', #1899/12/30#, [Duration]).
Now, define DurationStr as
This will display the integer as hh:mm:ss (even if it is more than 1 day - it will show more than 24 hours, respectively).
Another subtlety I just noticed is that you had all entries by agent grouped together, and only one Agent ID per group. I don't think Tableau can read that smoothly - it will produce lots of nulls. So, I edited your Excel sheet a bit and added a column "Agent ID by Line", where the Agent ID is repeated for each line/reason code entered - they are not grouped together.
Now, drag Reason Code to Columns, Agent ID by Line to Rows, DurationInt to Text and DurationStr to Text. You'll see both total times (hh:mm:ss) and percentages reported.
Click on Analytics, drag Grand Total to columns. You'll also see the totals per agent.
Attaching a 9.1 workbook - let me know if you have more questions.
TotalingTimes.twbx 35.2 KB
Thank you Stoyko. It works accordingly, much appreciated your help.