Krishna -- Branching this to a new thread.
Grand totals of aggregates are flaky sometimes.
In the attached I made my own grand total this time.
Sheet 2(2) is a copy of your original. I added my own grand total calc, which is a window_sum of your AGG() calc. When it's formatted using 00:00:00 like your calc, I get the right number. But there is in per Task Type (which you need to have on the sheet to get the individual values.)
Right click on my calc and select "Edit Table Calc". You will see this:
Notice that I selected Task Type instead of relying on TABLE(down). Right now on this sheet the two are equivalent, but when move task type off ROWS, this setting becomes important.
Go to sheet (3). Here I took [hh:mm:ss] off TEXT, and I dragged [Task type] from ROWS to Details. Now I get x-many copies of my calc. (One per Task Type.)
Go to Sheet (4). Here I made a calc called [index]. I set the table calc setting on this to match my Calculation2 setting. And then I selected for value = 1. Now we just see one copy of the calc value.
It's a long route to do what you expected to see when you just took [Task Type] off the sheet.
One thing that was happening with task type off the sheet is that the AVG([Time in sec]) changes from the average for each task type to the average of all the rows. With that, the calculation for [hh:mm:ss] would be using the wrong starting value. that's why we needed to do table calcs with Task type added into the mix.
TAT_format_SM_10.3_v10.3.twbx 94.2 KB
I'm not sure why the grand total on sheet 2 shows 161:59:34. In your original workbook is showed 161:18:93. And the table calc gets that value.
And even when I look at that number, why doesn't it calc to 161:19:33?
I'm not that familiar with converting integer values of seconds into HH:MM:SS using the formatting like this. But I can tell you that the 161:18:93 is what you would get if you add up the individual numbers by hand on sheet 2.
I have worked upon the calculation in the latest attached workbook to eliminate discrepancies. i.e, I wanted to display in hh:mm:ss format. So, I have edited accordingly.
none of the time hh:ss: must exceed 60
The very first sentence in my first reply is probably why the grand total on Sheet 2 doesn't give you the same value as the viz in sheet 3. Grand Totals of aggregates are flaky sometimes. If your measure on sheet 2 were just displaying [Time in sec] as a raw number, then the grand total would sum up correctly. But [hh:mm:ss] is an aggregate calc because you are aggregating (AVG) in it. Trust the table calc on sheet 3. Display that and don't rely on the Grand Total from Sheet 2.
But my requirement is to display the report in this format.
The below two labels Have been created in separate worksheets and placed on the dashboard.
The label below gives wrong info. Whereas it must display 8days 17hours 5 minutes and 13 seconds.
I used the below formula to display the grand total but it doesn't aggregate further.
IIF([Avg Time in seconds] % 60 == 60,0,[Avg Time in seconds] % 60)// seconds
+ IIF(INT([Avg Time in seconds]/60) %60 == 60, 0, INT([Avg Time in seconds]/60) %60) * 100 //minutes
+ IIF(INT([Avg Time in seconds]/3600) % 24 == 0, 0, INT([Avg Time in seconds]/3600) % 24) * 10000 //hours
+ INT([Avg Time in seconds]/86400) * 1000000 // days
It displays the result as you see in image1.
Like I said before, I'm not that familiar with formatting an integer value of seconds into days and hours, etc. If I were doing that I would probably break it apart into separate tokens (days, hours, minutes, etc.) and concatenate them into a string. (And I know that's not the most efficient way, but it's still how I would approach it.)
What I've been answering in this thread is helping you get a reliable total value, which is to do it with the table calc I provided.
I'm going to have to leave the formatting of that value up to you.
But as for displaying the Sheet 3 total, put it on the dashboard as a separate sheet and size/format it to look like it's part of the overall data sheet.