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.

https://app.smartsheet.com/b/publish?EQBCT=e14a739a1b7743ea9e4c34f03ac085d6 (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)

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

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))), "")

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