How to Calculate Mode in Power BI

Calculating the mode in Power BI isn't as straightforward as finding an average using the simple AVERAGE() function, but it's crucial for understanding the frequent high-fliers in your data. Whether you need to find your most popular product, the most common support ticket reason, or the most frequent order size, identifying the mode is a fundamental piece of analysis. This article will walk you through exactly how to calculate the mode for both number and text columns using DAX.

What is the Mode, and Why Does it Matter?

In statistics, the mode is the value that appears most often in a set of data. It's the simplest measure of central tendency to understand. For example, in the data set {2, 5, 8, 5, 1, 5, 9}, the mode is 5 because it shows up more than any other number.

The concept applies equally to non-numeric data. If you were analyzing customer survey feedback with responses like {"Good", "Poor", "Good", "Excellent", "Good"}, the mode would be "Good".

In a business context, the mode helps you answer important questions like:

• What is our best-selling shoe size?
• Which marketing campaign is mentioned most frequently in our sign-up form?
• What is the most common deal size our sales team closes?
• Which city do most of our customers live in?

Unlike averages (the mean) or medians (the middle value), the mode highlights popularity and frequency, making it incredibly valuable for inventory management, marketing strategy, and operational improvements.

Why Is There No Simple MODE() Function in DAX?

If you're coming from Excel, you might be looking for a simple MODE() function in Power BI's DAX language. It’s a common point of confusion for new users to find that it doesn't exist. Why not?

The reason lies in how DAX is designed. DAX is a functional language built around aggregation and context. Functions like SUM(), AVERAGE(), and COUNT() perform a calculation over an entire column of data within the current filter context (like a specific year, product, or region).

Calculating the mode is a bit more complex. It's a two-step process:

1. First, you have to count the frequency of every single distinct value in the column.
2. Then, you have to find out which of those values has the highest count.

Because this logic requires iterating through grouped values rather than performing a simple aggregation, we need to build our own calculation using a sequence of DAX functions.

Method 1: Calculating the Mode for a Numeric Column

Let's say we have a sales table named Sales and we want to find the most common number of items purchased in a single order, using a column called Units Per Order. Here’s a robust method to create a measure that finds the mode for a numeric column.

Step 1: Open Power BI and Create a New Measure

In Power BI Desktop, right-click on your table in the 'Data' pane and select "New measure". This will open the formula bar where you can enter your DAX expression.

Step 2: Enter the DAX Formula

Use the following DAX pattern. Replace 'Sales' with your table name and [Units Per Order] with the numeric column you want to analyze.

Mode - Numeric =
VAR SummaryTable =
    SUMMARIZE (
        'Sales',
        'Sales'[Units Per Order],
        "Count", COUNTROWS('Sales')
    )
VAR TopValue =
    TOPN (
        1,
        SummaryTable,
        [Count],
        DESC
    )
RETURN
    MINX(TopValue, [Units Per Order])

Step 3: Understanding the Formula

Let's break down what this measure is doing, one variable at a time:

• VAR SummaryTable = SUMMARIZE(...): This line creates a virtual table variable named SummaryTable. The SUMMARIZE function groups our Sales table by the unique values in the [Units Per Order] column. For each of these unique values, it calculates the COUNTROWS('Sales') to find out how many times that value appears and stores it in a new temporary column named "Count". The result would look something like this in the background:
• VAR TopValue = TOPN(1, SummaryTable, [Count], DESC): This second variable uses the TOPN function to sort our virtual SummaryTable. It looks for the top row (1) ordered by the "Count" column in descending (DESC) order. This effectively isolates the single row with the highest frequency count.
• RETURN MINX(TopValue, [Units Per Order]): Finally, the RETURN statement gives us our output. Since TopValue is still technically a table (even though it's just one row), we need a way to extract the value we care about. MINX (or MAXX) iterates over our single-row table and returns the value from the [Units Per Order] column. And there's your mode!

Step 4: Use Your New Measure in a Visual

Now you can drag your Mode - Numeric measure into a Card visual to see the number pop up on your report canvas. You have successfully calculated the most frequent value in your numeric column.

Method 2: Calculating the Mode for a Text Column

The great news is that calculating the mode for a text column uses almost the exact same logic. Let's say we have customer support data in a table called SupportTickets and a column named Reason that contains text values like "Billing Inquiry," "Password Reset," etc.

You can create a new measure and use a nearly identical formula:

Mode - Text =
VAR SummaryTable =
    SUMMARIZE (
        'SupportTickets',
        'SupportTickets'[Reason],
        "Count", COUNTROWS('SupportTickets')
    )
VAR TopValue =
    TOPN (
        1,
        SummaryTable,
        [Count],
        DESC
    )
RETURN
    MINX(TopValue, [Reason])

As you can see, the only change was replacing the table and column names to target our categorical data. The underlying DAX pattern of SUMMARIZE, TOPN, and MINX/MAXX is versatile and works for both data types.

Advanced Topic: How to Handle Ties (Multimodal Data)

One critical question is: what happens if two values are tied for the most frequent? For example, if both "Password Reset" and "Billing Inquiry" appeared exactly 500 times.

The TOPN(1, ...) approach shown above will arbitrarily pick one of the tied values to display, which can be misleading. If you need to see all modes, we need a more advanced DAX pattern.

This measure will return a comma-separated list of all modal values.

Mode - With Ties Handled =
VAR SummaryTable =
    ADDCOLUMNS (
        SUMMARIZE ( 'SupportTickets', 'SupportTickets'[Reason] ),
        "Frequency", CALCULATE ( COUNTROWS ( 'SupportTickets' ) )
    )
VAR MaxFrequency =
    MAXX ( SummaryTable, [Frequency] )
VAR ModesTable =
    FILTER ( SummaryTable, [Frequency] = MaxFrequency )
RETURN
    CONCATENATEX ( ModesTable, [Reason], ", " )

Breaking Down the "Ties Handled" Formula

• VAR SummaryTable = ADDCOLUMNS(...): This combination first gets the list of unique values with SUMMARIZE, then uses ADDCOLUMNS to add a new column named "Frequency" which holds the count for each value. This often performs better and is more readable than the first approach.
• VAR MaxFrequency = MAXX(...): This is straightforward. It finds the highest count from our SummaryTable's "Frequency" column.
• VAR ModesTable = FILTER(...): This creates a new virtual table called ModesTable. It goes through the SummaryTable and keeps only the rows where the "Frequency" is equal to the MaxFrequency we just found. If there's a tie, this table will contain multiple rows.
• RETURN CONCATENATEX(...): This is the key to handling ties. CONCATENATEX iterates through our ModesTable and joins the values from the [Reason] column, separating each one with a comma and a space. Now, if "Password Reset" and "Billing Inquiry" are tied, your visual will display "Password Reset, Billing Inquiry".

Final Thoughts

Although Power BI lacks a one-click MODE() function, creating a robust mode measure with DAX is entirely achievable. By using powerful functions like SUMMARIZE, TOPN, FILTER, and CONCATENATEX, you can accurately identify the most frequent values in both numeric and text data, and even handle situations where there are ties for the top spot.

Building these DAX measures is a valuable skill, but it also reflects the manual lift sometimes required to get straightforward business answers from your dashboards. At Graphed, we remove this friction entirely. Instead of writing DAX, you can simply connect your data sources - like Shopify, Google Analytics, or your CRM - and ask in plain English, "show me my most frequent product category" or "create a dashboard of my top sales regions". Our AI-powered analyst builds the real-time dashboard or gets you the answer in seconds, translating your everyday business questions into the powerful insights you need without the technical overhead.