Dashboard formula for multiple metrics

Ashlie S

Hi Friends,

It has been a long day of formula creation and I am stumped on one of them. I am creating formulas for a dashboard so I am working on my Master Matric Sheet. I have a source sheet that I am pulling from.

What I need to write is a formula that pulls from the source sheet and pulls the amount of days per State from Cohort Start Date to Cohort Exit date based on the status of graduate.

Compare start date with exit date by state and average

Everything I write comes back invalid or unparable

Any help would be appreciated. Thank you!

jmyzk_cloudsmart_jp

Hi @Ashlie S

You can create a dashboard with a metric sheet or reports with filters, grouping, and summary.

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

https://app.smartsheet.com/b/publish?EQBCT=4fd869627d1e47769a1e037feaff1bfc (Base Data Sheet Example)

I added a "Duration" helper column to make summing easy.

Metric Sheet

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

I added the [#] helper column to populate the States by formula in the metric sheet dynamically. (You can avoid this if you will be listing all 50 states manually.)

Then, you can use the SUMIFS function to get the Duration or "amount of days."

[State] =IFERROR(INDEX(DISTINCT({State}), [#]@row), "")
[Graduate] =IF(ISTEXT(State@row), SUMIFS({Duration}, {State}, State@row, {Status}, "Graduate"))
[Incomplete] =IF(ISTEXT(State@row), SUMIFS({Duration}, {State}, State@row, {Status}, "Incomplete"))
[Withdrawn] =IF(ISTEXT(State@row), SUMIFS({Duration}, {State}, State@row, {Status}, "Withdrawn"))

Reports

Alternatively, you can use the report's filter, group, and summary features to avoid tackling formulas.

For example, the third chart in the dashboard uses the report below, which is filtered by Status = Graduate, Grouped by State, and Summarized by Duration.

You can copy this report to create reports for All (remove the filter) and other Status like Incomplete (change the filter), then display them in the dashboard.

jmyzk_cloudsmart_jp

Hi @Ashlie S

You can create a dashboard with a metric sheet or reports with filters, grouping, and summary.

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

https://app.smartsheet.com/b/publish?EQBCT=4fd869627d1e47769a1e037feaff1bfc (Base Data Sheet Example)

I added a "Duration" helper column to make summing easy.

Metric Sheet

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

I added the [#] helper column to populate the States by formula in the metric sheet dynamically. (You can avoid this if you will be listing all 50 states manually.)

Then, you can use the SUMIFS function to get the Duration or "amount of days."

[State] =IFERROR(INDEX(DISTINCT({State}), [#]@row), "")
[Graduate] =IF(ISTEXT(State@row), SUMIFS({Duration}, {State}, State@row, {Status}, "Graduate"))
[Incomplete] =IF(ISTEXT(State@row), SUMIFS({Duration}, {State}, State@row, {Status}, "Incomplete"))
[Withdrawn] =IF(ISTEXT(State@row), SUMIFS({Duration}, {State}, State@row, {Status}, "Withdrawn"))

Reports

Alternatively, you can use the report's filter, group, and summary features to avoid tackling formulas.

For example, the third chart in the dashboard uses the report below, which is filtered by Status = Graduate, Grouped by State, and Summarized by Duration.

You can copy this report to create reports for All (remove the filter) and other Status like Incomplete (change the filter), then display them in the dashboard.

Ashlie S

Thank you. What I was missing was my helper column on the source sheet. Once I created that my written formula worked.

jmyzk_cloudsmart_jp

Glad you worked it out!😁