Creating a bar chart showing task owner name and the count of tasks (25 possible task owners)

Sonnie

Hi,

I am creating bar chart showing the names of task owners and the count of tasks assigned to them.

I achieved this by listing the task owner names in my metrics sheet and getting the count by using the countif formula.

But now I am told there is 25 task owners (instead of 5).

So, I updated the metrics sheet with all the task owners and copied the formula down.

But now I am seeing the 25 task owner names with most of them having zeros against them (most of them have no tasks assigned)

I want all the task owner names with zero task to disappear from the chart; I also want the chart to be sorted, so that the task owner with the maximum no of tasks is at the top - I wish chart had settings to do this, but I think it is not there

Instead of metrics sheet I tried with report. It is kind of working in the report, as I am able to filter out task owners with no tasks assigned.

But still I am not able to create a chart out of it to show the task owner and the count. of tasks

Please let me know your ideas. 😎

jmyzk_cloudsmart_jp

Hi @Sonnie

In the demo solution below, I first created a Metrics sheet that lists distinct owners with rank based on the number of tasks. Then, from the sheet, I made a Metrics Sheet reordered by the rank.

Smartsheet

(Link to the published demo dashboard)

Ranking the Metrics sheet: Distinct Owners

I used those formulas to rank based on the number of tasks, coping with the tie.

[Owner] =IFERROR(INDEX(DISTINCT({Task List (25 Owners) Range : Assinged To}), [No.]@row), "")
[Task Count] =COUNTIFS({Task List (25 Owners) Range : Task}, NOT(ISBLANK(@cell)), {Task List (25 Owners) Range : Assinged To}, Owner@row)
[Rank1] =RANKEQ([Task Count]@row, [Task Count]:[Task Count], 0)
[Rank2] =RANKEQ([No.]@row, COLLECT([No.]:[No.], [Rank1]:[Rank1], [Rank1]@row), 1) - 1
[Rank] =IF([Task Count]@row > 0, [Rank1]@row + [Rank2]@row)

Smartsheet

Reordering the Metrics sheet by the Rank

Then, using the rank, I reordered the metric sheet.
The critical point here is the [Row Index] column, which looks at the Rank column and gets the [No.] column value whose Rank value matches the [Order] value.

[Row Index] =IFERROR(INDEX(COLLECT({Metrics sheet : Distinct Owners Range : No.}, {Metrics sheet : Distinct Owners Range : Rank}, Order@row), 1), "")

[Owner] =IFERROR(INDEX({Metrics sheet : Distinct Owners Range : Owner}, INDEX({Metrics sheet : Distinct Owners Range : No.}, SMALL({Metrics sheet : Distinct Owners Range : Rank}, [Row Index]@row))), "")

[Task Count] =IFERROR(INDEX({Metrics sheet : Distinct Owners Range : Task Count}, INDEX({Metrics sheet : Distinct Owners Range : No.}, SMALL({Metrics sheet : Distinct Owners Range : Rank}, [Row Index]@row))), "")

Smartsheet

Sonnie

Thanks @jmyzk_cloudsmart_jp 😀

I will try this

Paul Newcome

You shouldn't need the second metrics sheet at all. Just create your original, include a ranking solution (whichever works best for you out of the many that are posted here in the Community forum), then create a report that filters out zeros and sorts by rank. The report can then feed your chart.

Sonnie

Thanks @Paul Newcome