The median is the middle value in a sorted list of numbers. If there is an even number of values, it will return the average of the two middle numbers.
Syntax:
MEDIAN(<column>)
Parameters:
Column: The column that contains the numbers for which the median is to be computed.
Note:
- Only the numbers in the column are counted.
Blanks are ignored. Logical values, dates, and text are not supported. - This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
Sales Dataset
| SaleID | SalesAmount |
| 1 | 100 |
| 2 | 150 |
| 3 | |
| 4 | 200 |
| 5 | |
| 6 | 250 |
| 7 | 300 |
Let’s get started. Follow the steps below:
Step-1: Load the dataset above into the Power BI report.
Step-2: Create a measure to calculate the Median of the Sales amount.
Right-click on the dataset name and select New Measure.
Median Sales = MEDIAN(Sales[SalesAmount])
Median ignores the blank values, and for the above dataset, the SalesAmount values are [100, 150, 200, 250, 300]. The median value will be 200, as it is the middle value in the ordered list.
You can see the result of the median in the screenshot below.
Another example:
Now, I have added one more row to the dataset. See the screenshot below for your reference.
Steps to Calculate the Median:
-Remove the blanks: The empty values in rows 3 and 5 will be ignored during the median calculation, and the median will be based on the non-empty values: [100, 150, 200, 250, 300, 200].
-Sort the values in ascending order: [100, 150, 200, 200, 250, 300]
-Find the median:
- If the dataset has an even number of values, the median is the average of the two middle values.
- The middle values here are 200 and 200.
- Median = (200 + 200) / 2 = 200.
Refer more DAX function – DAX Tutorials
