Novice user-Basic Dashboard fields

Pat Deignan

I'm a novice user trying to figure this out myself who has built my first Sheet with Tasks, Name, and % Complete. I have ~300 Tasks. For reporting-out purposes, I want to summarize onto a Dashboard by Name, and each name's Task Count and overall % Complete. I created Reports for each Name with Task Count and Avg % Complete summaries, and thought this was the right step thinking this is a needed interim step to summarize data that could then go into a Dashboard. Is that typically how it's done? But I'm not figuring out how to use Widgets to get this Report data into a Dashboard. I've also thought about just summarizing the Sheet data into one Report for my "reporting-out" purposes, but I have to figured out how to do that.

I'm an individual subscriber, so maybe features aren't available to me to do these summary reporting-out functions with either a Dashboard or a Report? I've read many articles, but they circle me back to what I've already tried. Any help and direction is appreciated! Thanks.

Genevieve P.

Hi @Pat Deignan

The Grouping and Summary features in Reports are new, so they currently aren't supported in Dashboard Chart or Report widgets (but our Product Team is working on this!) The interim solution is to Publish the Report and embed it as a Web Content Widget, but the Publishing feature is only available on multi-user plans.

As an alternative, you may want to set up a "Metrics" sheet where you can use formulas to gather the same data, the step between the source sheet and the dashboard. For example, you can set up a cross-sheet COUNTIFS function to count the number of rows that meet a certain criteria (like the Task Name). You can also use an AVG(COLLECT combination to find averages in another sheet.

Here are some articles which may help you build this out:

• Cross-Sheet Formulas
• COUNTIFS function
• Dashboards part 1 - Preparing data

I'd be happy to help you build out these formulas if you'd like, but it would be useful to see a screen capture of the source sheet (either with dummy data or with sensitive information blocked out) so I can identify the column names & types to figure out the structure needed in the formula.

Cheers!

Genevieve

Genevieve P.

Hi @Pat Deignan

The Grouping and Summary features in Reports are new, so they currently aren't supported in Dashboard Chart or Report widgets (but our Product Team is working on this!) The interim solution is to Publish the Report and embed it as a Web Content Widget, but the Publishing feature is only available on multi-user plans.

As an alternative, you may want to set up a "Metrics" sheet where you can use formulas to gather the same data, the step between the source sheet and the dashboard. For example, you can set up a cross-sheet COUNTIFS function to count the number of rows that meet a certain criteria (like the Task Name). You can also use an AVG(COLLECT combination to find averages in another sheet.

Here are some articles which may help you build this out:

• Cross-Sheet Formulas
• COUNTIFS function
• Dashboards part 1 - Preparing data

I'd be happy to help you build out these formulas if you'd like, but it would be useful to see a screen capture of the source sheet (either with dummy data or with sensitive information blocked out) so I can identify the column names & types to figure out the structure needed in the formula.

Cheers!

Genevieve

Pat Deignan

Thanks! I'll take a look at the articles and your suggestions. I may come back to you for assistance as you suggested. How do I contact you directly?

Genevieve P.

Hi @Pat Deignan

If you @ mention me in your post I'll receive a notification 🙂

Pat Deignan

Thanks @Genevieve P !

Pat Deignan

Hi @Genevieve P

Attached is PDF of source Sheet.

Also, in Excel, an example of Dashboard output I'd like to get to (Grid format). Then if I can also present in Chart format, even better.

Test Sheet (1).pdf

Smartsheet Metrics and Dashboard example 02.25.21.xlsx

Thanks!

Genevieve P.

Hi @Pat Deignan

This is great! You have your sheet set up well, so it will be a pretty easy formula to put into your "Task Count" column.

The way to do this will be to set up a sheet exactly as you have that Excel document above, with three columns: Name / Task Count / % Complete

Then you will want one "helper" column which creates the labels for your chart, adding together the Name and the % Complete into one cell.

Make sure that the Name column has the data all the way down the column so that eventually your sheet looks more like this (note: I've coloured mine just for visual clarity):

This is the formula I used to create the Title column:

=Name@row + " - " + ([% Complete]@row * 100) + "%"

From here you can create a cross-sheet COUNTIFS statement to COUNT how many times the Name in the Assigned To column appears with the value in the % Complete Column:

=COUNTIFS({Assigned To in other sheet}, Name@row, {% Complete other sheet}, [% Complete]@row)

The ranges {in these} are Cross-Sheet references looking into your project sheet. The ranges that say @row are looking into the columns of this current Metric sheet. You just need to write this formula once and then you can drag-fill it down the whole column.

Then I would create a TOTALS section at the bottom for the summaries. These would just SUM the totals from the above data. Does that make sense?

From here you can create a number of different charts in your dashboard. Let me know if you still need help after reviewing the following articles:

• Cross-Sheet Formulas
• COUNTIFS function
• @row function
• Compare Live Data with a Chart
• Dashboards part 1 - Preparing data

Cheers!

Genevieve