Syntax of MEDIANX Function
The syntax of the MEDIANX function is as follows:
Here, `❰table❱` is the table or a table expression that you want to evaluate, and `❰expression❱` is the expression that is used to determine the median value.
How to Use the MEDIANX Function
To illustrate how to use the MEDIANX function, let's consider an example where we have a table named "Sales" that contains the following columns: "Region", "Product", and "SalesAmount". We want to calculate the median sales amount for each region.
1. Open Power BI Desktop and connect to your data source.
2. In the "Data" view, click on "Enter Data" and enter the following data for the "Sales" table:
| Region | Product | SalesAmount |
| East | A | 100 |
| East | B | 200 |
| East | C | 300 |
| West | A | 400 |
| West | B | 500 |
| West | C | 600 |
3. Click on "Transform Data" to open the Power Query Editor.
4. In the Power Query Editor, select the "Sales" table.
5. Click on the "Add Column" tab and select "Custom Column".
6. In the "Custom Column" dialog box, enter the following formula:
= MEDIANX(Filter(Sales, Sales[Region] = [Region]), Sales[SalesAmount])
Here, we are using the MEDIANX function to calculate the median sales amount for each region. We are filtering the Sales table to include only the rows that have the same region as the current row, and then calculating the median of the SalesAmount column for those rows.
7. Click on "OK" to add the custom column.
8. Click on "Close & Apply" to apply the changes and close the Power Query Editor.
Now, we have a new column in our Sales table that contains the median sales amount for each region.
In this article, we discussed how to use the MEDIANX function in Power BI to calculate the median value of an expression. We provided an example of how to use this function to calculate the median sales amount for each region in a table. With this knowledge, you can now apply the MEDIANX function to your own data in Power BI to gain valuable insights.
Power BI DAX Training Courses by G Com Solutions (0800 998 9248)
Power BI DAX Intensive Training Course£1,050.00 – £26,550.00
Power BI DAX Introduction£395.00 – £9,750.00
Power BI DAX Intermediate£395.00 – £9,750.00
Power BI DAX Advanced£395.00 – £9,750.00