Metric Widget without Summary Sheet Report?

Rebecca BdR

I'm trying to figure out a formula-free way to show summarized info. 😅

I have 3 Cities on my Sheet; each city has 'co-parent' rows, and under these co-parent rows are Tasks that have a Status. I'd like to have a Metric widget on my dashboard that shows the number of Tasks per Status, per City - for example, 'Tulsa' In Progress - 3, Blocked - 1, Complete - 2; 'Phoenix' Not Started - 1, At Risk - 2. I'm seeing it doesn't seem possible without a Sheet Summary, but I really have no idea how the formulas would work on that! I've made a regular Row Report and hope to be able to use that. If this doesn't make sense, please LMK and I can hopefully clarify more!

jmyzk_cloudsmart_jp

Hi @Rebecca BdR

Suppose your sheet is like the one below, except for the City column.

https://app.smartsheet.com/b/publish?EQBCT=cbd92ee440124fe39d79b3329f537c75

To create a summary by city and status, we need a city value corresponding to the status value.

So, we add a helper column, City, and get the Parent or Grand Parent data in the Task Name column, using the following formula.

[City] =IF(ISTEXT(Status@row), INDEX(ANCESTORS([Task Name]@row), 1))

The IF pat is to show the city value if the Status is not blank, so NOT(ISBLANK(Status@row)) is also acceptable.

The ANCESTORS function gets all the parents' or ancestors' values as a range, and by specifying to get the top one by the INDEX function, we can get the city value, if that is the top of the hierarchy.

If you already have the City column and corresponding values, you can omit the formula part.

From this sheet, you can create a master report that includes all cities.

Then, from the report's Fike menu, you can use the "Save as New" menu to create copy of the report for each city.

In the newly copied or created report, change the filter to select only one city, Tulsa, for example.

You can use this report to show charts or a report in a dashboard.

In the dashboard below, I first created a semi-circle pie chart, copied the chart, and changed it to a bar chart.

https://app.smartsheet.com/b/publish?EQBCT=a1714a19e6f34e459603960ce6ce08a8

You can repeat the same process for other cities.

Though not completely "formula-free way", I guess this method lets you create a dashboard with a minimum formula.

Rebecca BdR

So I must learn to get better at formulas, regardless 😅 With that said, on the dashboard you have, you used the Report and 2 Chart widgets - I'd like to use the Metric widget. Is that possible from what you showed me? Also, THANK YOU SO MUCH!!! 🎉

Rashmi Shekhar

Hi @Rebecca BdR and @jmyzk_cloudsmart_jp, I am the Product Manager at Smartsheet for Charts and Metric Widgets and I just wanted to chime in here.

Metric widgets currently do not support data from Reports. Once you have the helper column, you will need to create a separate sheet that houses the calculations (COUNTIF) for each city and status. Then use that sheet as the data source for your metric widget.

Today's process for creating charts and metrics is heavily reliant on our customers knowing how to use formulas, and we understand that it is unintuitive and cumbersome. We are working on a new creation experience that enables the user to select the aggregations (sum, count, avg etc) they want from a dropdown and not have to write out formulas. This experience will begin with charts, and then extend to metric widgets in the future. If either of you are interested previewing these new concepts and giving us your feedback, I would love to set up a call with you. It would greatly help us shape the solution. Let me know, and thank you either way!

-Rashmi

Rebecca BdR

I would LOVE to help with the previewing, thank you so much, both of you!!!

Rebecca BdR

Hi again @jmyzk_cloudsmart_jp!

I'm not getting the same bar chart you show on your screenshot - if you have capacity would appreciate your help on that!

jmyzk_cloudsmart_jp

I am happy to help! @Rashmi Shekhar

@Rebecca BdR

Copy

Paste and edit

Select bar

Check off "Switch rows & columns"

Rashmi Shekhar

jmyzk_cloudsmart_jp @Rebecca BdR That's great, thank you! I'll send each of you a DM to set up time.

SueinSpain

So with metrics widget I have found I can either create summary fields and access them or you can indent your rows with a =COUNT(CHILDREN()) as long as you have organised your data OK.

Summary field "Tulsa In Progress" with formula =COUNTIFS(City:City, = "Tulsa", Status:Status, ="In Progress"

Summary field "Tulsa Blocked" with formula =COUNTIFS(City:City, = "Tulsa", Status:Status, ="Blocked" etc. it is very longwinded to set up but once done you can either access by a summary report for charts or the field directly for Metrics.

Hope this helps & good luck