How to Calculate Median in Power BI

Calculating an average is easy, but it won't always give you an accurate picture of your data. The median, or middle value, is often a much more reliable metric for understanding typical customer behavior or performance, especially when you have outliers. This article will show you exactly how to calculate the median in Power BI using straightforward DAX formulas like MEDIAN and MEDIANX.

Why the Average Can Be Deceiving and Why Median Matters

In most Power BI reports, the go-to function for summarizing data is typically AVERAGE. You want to know the average order value, average session duration, or average deal size. It’s simple and everyone understands it. But the average can be a bit of a statistical liar.

The average (or mean) is calculated by adding up all your values and dividing by the count of those values. The problem is that it is highly sensitive to outliers - extremely high or low values that aren't representative of the overall dataset.

Imagine you run an e-commerce store and you want to find your typical order value. Your recent orders look like this:

• $50
• $65
• $45
• $70
• $55
• $900 (a large-volume wholesale order)

If you calculate the average order value, you get $197.50. Does that feel right? Not really. Five of the six orders were under $75. That one huge order of $900 completely skewed the result, making you think your typical cart size is much larger than it actually is. This is where the median comes in to save the day.

What is the Median?

The median is the middle value in a dataset when it's sorted in ascending order. It’s the true halfway point. To find it, you just line up your numbers from smallest to largest and pick the one in the middle.

Let's use our same order values and sort them:

$45, $50, $55, $65, $70, $900

Since we have an even number of values (6), there isn't one single middle value. In this case, we just take the average of the two middle values, $55 and $65.

($55 + $65) / 2 = $60

The median order value is $60. This number does a much better job of representing the "typical" order in our dataset than the average of $197.50. It ignores the extreme outlier and gives us a grounded, reliable metric we can use for decision-making.

Whether you're looking at sales data, website traffic, or marketing campaign performance, the median helps you find the true center of your data and avoid being misled by outliers.

How to Calculate Median in Power BI

You may have noticed that when you drag a numerical field into a visual in Power BI, the default summarization options include Sum, Average, Minimum, Maximum, and Count. But where is Median? Unlike the others, there isn't a one-click summarization option for it. This is because calculating a median requires Power BI to look at the entire dataset within the current filter context, sort it, and then find the middle - a process that's more complex than a simple sum or average. To get the median, we need to write a little bit of DAX (Data Analysis Expressions). Don't worry, it's a lot easier than it sounds.

Power BI has two primary DAX functions for this: MEDIAN() and MEDIANX().

Using the Simple MEDIAN() Function

The MEDIAN() function is the most straightforward way to calculate the median. It works directly on a single column of data. You give it a column, and it returns the median value from that column.

The syntax is incredibly simple:

MEDIAN(<columnName>)

Step-by-Step Example: Finding the Median Order Value

Let's assume we have a table called 'Sales' with a column named 'OrderValue'. Here’s how you’d create a measure to calculate the median order value.

1. On the Home ribbon in Power BI Desktop, click on New Measure.
2. The formula bar will appear at the top. This is where you'll write your DAX formula.
3. Type in the following DAX expression:

Median Order Value = MEDIAN('Sales'[OrderValue])

Let's break that down:

• Median Order Value = This is just the name you're giving your new measure.
• MEDIAN() is the DAX function we're using.
• 'Sales'[OrderValue] tells Power BI to look at the 'Sales' table and use the 'OrderValue' column for the calculation.

Once you press Enter, your new measure will appear in the Fields pane on the right-hand side, usually marked with a calculator icon. Now you can use it just like any other field!

You can drag it into a Card visual to see the overall median order value, or add it to a table or matrix to see it broken down by product category, date, or any other dimension in your model.

Going Further with the MEDIANX() Function

The MEDIAN() function is great, but it has a limitation: it can only operate on an existing column. What if the number you want to find the median of doesn't exist as a neat, single column in your table? For instance, what if you need to calculate a value for each row before finding the median?

This is where MEDIANX() comes in. It’s an "iterator" function, which means it iterates, or goes through a table, performs a calculation row-by-row, and then finds the median of the results of those calculations.

The syntax for MEDIANX() is:

MEDIANX(<table>, <expression>)

• <table>: The table you want the function to iterate over.
• <expression>: The calculation you want to perform for each row in that table.

Example 1: Calculating the Median Profit Per Order

Imagine your 'Sales' table has a 'Revenue' column and a 'Cost' column, but no 'Profit' column. You want to find the median profit. You can't use MEDIAN('Sales'[Profit]) because that column doesn't exist. Instead, you can use MEDIANX() to calculate the profit for each row first.

Create a new measure with this formula:

Median Profit = MEDIANX('Sales', 'Sales'[Revenue] - 'Sales'[Cost])

Here’s how Power BI processes this:

1. It looks at the 'Sales' table specified in the first part of the function.
2. For the first row, it calculates the expression 'Sales'[Revenue] - 'Sales'[Cost]. Let's say it's $100 - $60 = $40.
3. It goes to the second row and does the same calculation. Maybe it's $150 - $90 = $60.
4. It continues this row-by-row for the entire table, creating a temporary, in-memory list of profit values: [40, 60, ...].
5. Finally, it finds the median of this temporary list of profit values and returns that result.

This is extremely powerful because it lets you perform an analysis on a value that doesn’t physically exist in your database.

Example 2: Calculating Median Revenue From Price and Quantity

Here's another common scenario. Your 'Sales' table has 'UnitPrice' and 'Quantity' columns, but no pre-calculated 'TotalRevenue' column for each line item. You want to find the median total revenue per order.

You can easily do that with MEDIANX():

Median Total Revenue = MEDIANX('Sales', 'Sales'[UnitPrice] * 'Sales'[Quantity])

Just like before, this measure will loop through each row of the 'Sales' table, multiply 'UnitPrice' by 'Quantity' to get the total revenue for that row, and then find the median of all those calculated totals.

Using Your Median Measures in Reports

The true power of writing these median calculations as measures is that they are fully dynamic and respect the filters and slicers you have on your report page.

If you add a slicer for 'Country' to your report page and select "Canada," your Median Order Value measure will automatically recalculate to show you the median order value for only the sales made in Canada. Filter by a product category, a date range, or a customer segment, and your median values will update instantly to reflect that context.

This makes your median measures incredibly flexible analysis tools, giving you the ability to slice and dice your data to uncover insights without having to create dozens of separate calculations.

Final Thoughts

While the average is a useful starting point, the median often provides a truer reflection of your data by ignoring outliers. By mastering the MEDIAN() and MEDIANX() DAX functions, you can create more robust, accurate reports in Power BI that tell a clearer story about your business performance.

Manually building reports and mastering DAX can be time-consuming, but getting these kinds of insights without the headaches is why we created Graphed. We connect directly to your data sources like Google Analytics, Shopify, and Salesforce. Instead of writing formulas, you can just ask questions in plain English, like "What was our median order value last month?" We'll instantly generate the live dashboards and reports for you, so you can spend less time wrangling data and more time acting on it.