Displaying Summary text based on table data

I have a table with the below columns:

I want to display a summary text like the below:
Top 2 Zipcodes - 92122 - 2, 90001 - 2
Top 2 States - CA - 4, TN - 2

i.e the states and zipcodes need to be sorted by the frequency of occurrence and the most frequently occurring value needs to be displayed first.

How can I do this?

1 Like

This is a tricky one!

I think this should work for you…

Create a “Multiples” column and use the following formula:

[My Table].Filter(Zip=thisRow.Zip).Count()

Then for your canvas formula, use this:

[My Table].Sort(false,Multiples).FormulaMap(CurrentValue.Zip+" - "+CurrentValue.State).Unique().Slice(1,2)

Breakdown of the formula:

[My Table].Sort(false,Multiples). - checks the “Multiples” column and sorts the rows by “ascending:false”.
FormulaMap(CurrentValue.Zip+" - "+CurrentValue.State) - the FormulaMap runs through all rows and takes concatenates the current value’s zip, and state.
Unique() - Takes only the unique values
Slice(1,2) - Takes only the first two values. Change the “2” for however many you want to list.




Thank you, that worked!