How Do I Illustrate Checklist Data in a Dashboard?

megtro610 · 11/22/24

I am making a dashboard widget to show how many items in the implemented mitigations column are checked and how many are not. I understand that I may need to make a report or a sheet summary to do so first. How can I make this happen?

Also, at some point, I'd like to show a relation to, if it is implemented, is the evidence attached (column to the left).

Thank you

Melissa Yamada · 8:42AM

Hi @megtro610 ,

I'd be happy to help!

First, create a Metric Sheet (new sheet) and use cross sheet formulas to count for:

Implemented and Evidence Attached
1. Formula: =COUNTIFS({Is it implemented?}, 1, {Evidence Attached?}, "Yes")
Implemented and Evidence Not Attached
1. Formula: =COUNTIFS({Is it implemented?}, 1, {Evidence Attached?}, "No")
Not Implemented
1. Formula: =COUNTIFS({Status}, <>"", {Is it implemented?}, 0)

The formula accounts for:

Counting all rows that are implemented and evidence is attached based on the "Is it implemented?" and "Evidence Attached" columns.
Counting all rows that are implemented and evidence is NOT attached based on the "Is it implemented?" and "Evidence Attached" columns.
Counting all rows that are NOT implemented based on the "Is it implemented?" column and adding another criteria where "Status" is not blank to make sure that blank rows are not counted. (P.S. You can change this criteria to look at other column such as for example, "Description", "Item", "Tasks", etc.)

If you have a parent-child relationship on your sheet (Hierarchy) and want to only count the child rows, you need to add another helper column that will check if a row is a parent or a child. The formula you can use for this column is " =IF(COUNT(CHILDREN()) > 0, 1, 0) ."

Once helper column is added, you need to add this criteria on the formulas stated above: " {Parent?}, 0 "

The formulas will be:

Implemented and Evidence Attached
1. Formula: =COUNTIFS({Is it implemented?}, 1, {Evidence Attached?}, "Yes", {Parent?}, 0)
Implemented and Evidence Not Attached
1. Formula: =COUNTIFS({Is it implemented?}, 1, {Evidence Attached?}, "No", {Parent?}, 0)
Not Implemented
1. Formula: =COUNTIFS({Status}, <>"", {Is it implemented?}, 0, {Parent?}, 0)

Once the metric is done, you can now use this as data source for your Dashboard Widget. Here is a sample of a Pie Chart Widget.

If you want the data to visualize in a table form, you'd need to create a report out of the metric sheet and use it as the data source on a Report Widget.

You can check these articles for references:
https://help.smartsheet.com/learning-track/level-1-get-started/rows-and-hierarchy

https://help.smartsheet.com/learning-track/level-3-advanced-users/cross-sheet-formulas

https://help.smartsheet.com/articles/518558-widget-types-for-smartsheet-dashboards

Melissa Yamada · 8:42AM