Calculate Frequency and Mode of a Value in a Collection in Power Apps

The mode of a number or data set can be a useful statistic. There are some situations when the mean or average isn’t as helpful as the mode. The mode can give you the idea of a trend or most popular choice. Another example I’ve encountered is needing to identify the typical price of a good or service from large data sets in which there might be some variance due to discounts or human error.

Getting statistics like Mode and Standard Deviation are easy enough in Power BI or Excel. In Power FX there is a Standard Deviation function, StdP().

There’s no built in way to calculate the Mode in Power Automate or Power Apps, but here’s how you can do it.

Step One – Calculate Frequency of Distinct Items in Collection

In this example I’m using a collection called “gbl_col_prices” with two columns, price and title.

To get a distinct collection of the titles in the collection, I use Distinct()

Step Two – Add a Frequency Count Column

Use the AddColumns() function. The new column is called freq. The value for the new column is calculated by CountRows() on a filter of the original collection filtered by title = Value.

Value represents the Distinct Title from the previous step in the expression.

Then add a Sort() to sort by the new freq column in descending order.

Displayed in a gallery, the new collection of distinct titles with frequency counts now looks like this:

Step Three – Calculate Max Frequency

Use a Max() function on the distinct collection with added frequency count column we created in the steps above.

Max(<your_distinct_frequency_collection>,freq)
In my example, I display this in a text control like this:

Step Four – Find Modes

For most purposes, you can just filter your frequency collection for all items that match the max frequency, and take the first item to get your mode.

Technically, a set can have multiple modes, and you might want to display these, or take the middle mode.

In this example, I have a further break down where I check for frequencies of distinct prices for a select product, and display all the modes found in a gallery.

Expression for displaying all modes found in a gallery:

Expression for displaying first, middle and last mode found in a text control:

Thanks for reading!

If you found this post helpful please give it a like. Power Platform Developer | [LinkedIn | Wordpress | YouTube: Mark’s Power Stuff | Buy me a coffee