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?
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!
