What is the best way to display the Duration column on a dashboard when I have over 1500 rows ?

Christiana Gkini

Hello here,

I would like to display in a dashboard the duration column which is the time that the task took to be completed. What is the best way to display the Duration column on a dashboard, when I have over 1500 rows in my sheet? Is there a way to break it down by ticket type and take the average ?Could you please help me find the best way to show this?

I tried to do this as below, but not sure what these numbers really indicate. So ideally I would like to use the Duration Column (as shown in the picture above) as it is more clear.

Looking forward for some help here.

Thank you!

Itai

Hey @Christiana Gkini ,

The average in the report will show average days in the column. you can create a group average on the duration column.

Did you add the Smartsheet duration column or is that one you create yourself?

I would reccomend using the Smartsheet column - right click a column and go to Edit Project Settings. there you can add the duration and Predecessors columns that will make your life easier and will be able to average in the report as well.

Christiana Gkini

Hi Itai,

Thanks for getting back to me. I had to create it myself as things were more complicated.

However if I try what you said this is how it shows. 1st picture if I break the duration in Days, hours and minutes and 2nd picture when I try to get the average form the Duration column that I want it gives the notification NaN. Any chance you know how I can get the average in the Duration column and the best way to display this in the dashboard?

Samuel Mueller

@Christiana Gkini I think you need a helper column that combines days hours and minutes, then you could just average that column and group by type.

Something like a helper column named "Total Duration" with the formula

=days@row +hours@row/24 + minutes@row/1440

This will give you total duration in days, and if you average that in a report you would get average duration grouped by whatever group you choose.

Christiana Gkini

Hi Samuel,

Thanks for your advise.

I did the helper column and tried to group with the report as you said. However this appears so big in my dashboard.

I have decided to show only some of the ticket types in a sheet and with an average formula to get the average duration for the ticket types I want only. I have tried a couple of average formulas, but none of them is working. Could you show me which formula is the best in the case?

Looking forward for some help.

Thank you!

Samuel Mueller

@Christiana Gkini One option would be to just filter that same report only to the ticket types you want to see.

Otherwise, your formula should just be

=iferror(averageif({copy of snow range 3},[ticket type]@row, {duration}),0)

Samuel Mueller

Example of creating a report filter for ticket type

Samuel Mueller

For the formula option make sure you get the cross reference names right

=iferror(averageif({*ticket type column*},[ticket type]@row, {*duration helper column*}),0)

Christiana Gkini

Hi Samuel,

It worked thank you very much!

One last question please, this number indicates the average days, right?

Thank you!

Samuel Mueller

@Christiana Gkini if "{copy of snow Q1 Range 2}" is referencing the helper column you created with days hours and minutes, then yes it should be the average of days corresponding to the matching ticket types.

Christiana Gkini

Thank you very much for your help Samuel!

Have a great weekend!