# Get count unique, max element AKA how to get the biggest piece of a pie chart in a canvas formula

I am struggling to get the item with the max unique count in a given table.

I have a table with votes on the best speaker and want to display the speaker name with the most votes in a canvas formula.

How can I do this?
Many thanks!

Thereâ€™s the `sortByCount` argument in the `Sort` function that apparently should do this â€” but I couldnâ€™t get it to work. Doesnâ€™t seem like itâ€™s working at all never used it anyway.

The algo to do this would be a bit more complicated:

1. Identify unique entries
2. For each entry calculate the count
3. Get the entry(ies) with the biggest count

The easiest for newcomers is to do it with a separate table of Name â†’ Count. Youâ€™ll have to populate the table with all unique Names (perhaps with a button) and then calculate each personâ€™s count with a `Votes.Filter(CurrentValue.Name = thisRow.Name).Count()`. However thatâ€™d require a separate table and keeping it in sync with the list of names from the voting table.

A more advanced way would be to calculate this in a single formula without extra tables, but this requires some formula juggling:

``````Table.Column.WithName(Items,
// For each of the unique items collect tuples [Item, Count]
Items.Unique().ForEach(CurrentValue.WithName(Item,
List(
Item,
Items.CountIf(CurrentValue = Item)
)
))
).WithName(Tuples,
// Find the maximum count of the tuples (.Last() to take Count out of the [Item, Count] tuple)
Tuples.ForEach(CurrentValue.Last().ToNumber()).Max().WithName(MaxCount,
// Find the tuples that have the maximum count
Tuples.Filter(
CurrentValue.Last() = MaxCount
)
)
).WithName(MostCommonItemTuples,
// From each tuple extract the Item
MostCommonItemTuples.ForEach(CurrentValue.First())
)
``````

UPD: If you only need one top item and you donâ€™t care which it is if thereâ€™s multiple top items, hereâ€™s a much shorter solution with the function I keep forgetting about, `MaxBy()`:

``````Items.MaxBy(CurrentValue.WithName(Item,
Items.CountIf(CurrentValue = Item)
))
``````
2 Likes

Wow, thanks a ton for this Paul!
Both Options worked flawlessly. I would have never been able to come up with that.
Great to see where I can learn more about formulas.
Wish you a great start into the week:)

1 Like

Didnâ€™t mean to plug but you asked for it

Iâ€™m working on this guide on formulas â€” itâ€™s meant to teach you how to think about formulas so that you could write them like the one above. Itâ€™s not going very quickly though I havenâ€™t raised too much for it so I had to still take client work to stay afloat. But whenever I have time Iâ€™m working on it, and I do hope to launch it (or at least some of it) by the end of this month.

The usual resources are the Formulas list and the official guides but I only remember seeing simple examples there.

1 Like

Depending on what Is actually being graphed (and this only works for some graphs/datasets) you can always use Mode()

Mode() will bring out the most common element in a dataset

Then, after identifying that with Mode, go ahead and just filter the table against that calculation and count it! Nothing too fancy needed here.

2 Likes

Oh wow, TIL `Mode()` didnâ€™t only work on numbers. Another function I keep forgetting about

Thanks Scott!

P.S. Even though itâ€™s a valid solution (and in my book any trick can be a valid solution) I wouldnâ€™t use it, just like I wouldnâ€™t use `+` for concatenating strings. `Mode()` is a statistical function thatâ€™s meant to work on numeric samples (1, 2) along Min/Max/Mean/Median. The fact that it works on any values is IMO a byproduct of implementation and no checks â€” underneath Coda just collects a map of value â†’ count and doesnâ€™t force the value to be a number. While technically it works, semantically itâ€™s wrong. Perhaps thatâ€™s why I wouldnâ€™t think of it.

But then, replacing speaker names with 1, 2, 3 etc and using Mode on it â€” thatâ€™s valid and thatâ€™s clever!

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.