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:
- Identify unique entries
- For each entry calculate the count
- 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.