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 :person_shrugging: 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:

  // For each of the unique items collect tuples [Item, Count]
      Items.CountIf(CurrentValue = Item)
  // Find the maximum count of the tuples (.Last() to take Count out of the [Item, Count] tuple)
    // Find the tuples that have the maximum count
      CurrentValue.Last() = MaxCount
  // From each tuple extract the Item

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.CountIf(CurrentValue = Item)

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 :wink:

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 :frowning: 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.


Oh wow, TIL Mode() didn’t only work on numbers. Another function I keep forgetting about :sweat_smile:

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.