Future-Proof Your Career, Master Data Skills + AI

Blog

Blog

# Calculating Median Value Using DAX In Power BI

by | 9:00 am EDT | June 19, 2020 | Business Intelligence, DAX, Power BI

In this tutorial, I show you how to calculate the median value from a list of values inside Power BI using the MEDIANX function together with other DAX functions. I demonstrate how you can place a virtual table inside of this function, and then evaluate through that virtual table to obtain the ultimate result – the median result. You may watch the full video of this tutorial at the bottom of this blog.

With the many statistical functions that we can use in DAX, we need to understand how they calculate and how they operate over tables of values.

The median is the value separating the higher half from the lower half of a data sample (a population or a probability distribution). For a data set, it may be thought of as the “middle” value.

You might not want to set up your report like this, but I’ve set it up like so for you to understand the structure of this table. You’ll see how to create this table in a formula to then go and evaluate the result.

## Calculating Median Sales Using MEDIANX

The key function to use in the calculation is MEDIANX. This DAX function enables us to iterate through a table (physical or virtual). In this case, we’ll iterate through a virtual table because we want to calculate the median per state. We can’t put the Location table because it’s at a more granular level of detail, which is by city.

Since we’re evaluating at the state level, we need to somehow adjust our table. To do that, we use the SUMMARIZE function. This table function is clean and easy to implement inside iterating functions like MEDIANX.

We’re recreating this table here, State Code column, and this Total Sales column, but we’re calling it Sales Values.

So, at every row, we’re iterating through each state and working out the total sales. The median is going to happen across all of our sales once all of the iterating is done. And that’s how we achieve the State Median Sales.

## Another Way To Use The Formula

We can reuse the formula in many different ways. Here’s an example.

In this formula, we’re calculating for the Product Median Sales. All we need to do is change the virtual table that we’re iterating through inside of our iterating function (MEDIANX). And instead of creating a virtual table of the states, we create one with Product Name. The other column is still the Sales Value with Total Sales.

Once we’re through with all of those products, we then work out the median.

## Conclusion

You’ll find that these techniques in this tutorial are re-usable when completing other statistical analyses. This is a really good review of iterating functions in terms of how they work inside Power BI. You need to master these concepts if you are to utilize Power BI and DAX to the fullest.

Enjoy working through this tutorial. If you want some further studies around this type of DAX functionality inside of Power BI, check out the links below.

Cheers!

Sam

## How to Calculate Age in Excel: 5 Best Methods Explained

Looking to calculate age in Excel? Well, you're in the right place. Whether you need to find the age of...

## Funny ChatGPT Prompts: 20 Hilarious ChatGPT Ideas

In a world where technology continues to amaze us, we have now arrived at the point where we can have a...

## Power BI Slicer Search: User Guide With Examples

Ready to get started with the Power BI slicer? This feature will allow you to filter and slice your...

## How to Move Files in SharePoint: Top 4 Methods Explained

Struggling to move Files in SharePoint? Don't worry, we've all been there. Knowing how to move files...

## How to Return List in Python: 3 Top Methods Explained

The return statement plays a pivotal role in Python. It’s what lets your functions communicate with the...

## How to Create P Value in Excel? A Step-by-Step Guide

Ready to create a P value in Excel? Want to learn how to quickly and efficiently do it? Read on. To...

## How to Strikethrough Text in Excel? Easy Guide + Examples

Want to strikethrough some text in your Excel document but don’t know how? Don’t worry, it's easy, read...

## How To Calculate Z-Scores in Excel? User Guide with Examples

Ready to calculate a z-score in Excel? Awesome. Because we are going to show how to do it quickly and...

## Top 20+ Data Visualization Interview Questions Explained

So, you’re applying for a data visualization or data analytics job? We get it, job interviews can be...

## Top 10 Free Datasets for Statistics Projects

Are you looking for a way to explore and analyze real-world data to enhance your understanding of...

## How to Calculate Standard Error in Excel? Top 3 Methods

The standard error is a key statistical tool that allows you to assess the accuracy and reliability of...

## How to Delete a SharePoint Site: 4 Best Methods Explained

Ready to delete your SharePoint site? It really is as easy as 1,2,3 and 4! To delete a SharePoint site:...