How to Find Out Median for Clearer Insights?

DAX (Data Analysis Expression) functions in Power BI help in the creation of calculated columns, complex calculations, and measures. It aids powerful data manipulation, visualization, and analysis. That will, in turn, lead to effective decision-making.

One of the complex calculations you can perform using DAX functions is the DAX median. This guide will help you figure out how to find out median, why you should calculate the median in Power BI, and the advantages of calculating the median DAX.

Table of Contents:

1. What is the DAX Median?
2. Understanding Median in Power BI
3. Why You Should Calculate the Median in Power BI?
4. How to Find Out Median in Power BI?
5. How to Create a Visualization in Power BI?
6. What is the Best Practice for Using Power BI Median?
7. What are the Advantages of Calculating the Median DAX?
8. Wrap Up

First…

What is the DAX Median?

Definition: The MEDIAN function in DAX calculates the median value of a set of numbers. It’s the middle value in a sorted list of numbers, or the average of the two middle values if the list has an even number of elements.

In DAX, the MEDIAN function is used to calculate the median of a dataset. The formula syntax is:

• MEDIAN(<column>)

The function returns the median value of the specified column. For instance:

• MEDIAN(Table[Column])

Understanding Median in Power BI

Definition: The median in Power BI represents the middle value of a dataset when arranged in ascending or descending order. It calculates the (median) value for a given column and provides insights into the central tendency of a data distribution.

Why You Should Calculate the Median in Power BI?

The median provides a robust measure of central tendency that is less influenced by outliers compared to the mean. That comes in handy when dealing with Power BI datasets that contain extreme (or skewed) values.

It’s also used in conjunction with other statistical measures to provide a comprehensive understanding of the characteristics of your dataset.

How to Find Out Median in Power BI?

You can find the median using the MEDIAN function within a calculated column or measure. Here’s how to do just that.

Using a Calculated Column

Navigate to the dataset in Power BI Desktop, and go to the “Modeling” tab in the ribbon. Next, click on “New Column” in the Calculations group. Enter the formula using the MEDIAN function, and reference the column for which you want to calculate the median. For instance:

MedianColumn = MEDIAN(’Table’[Column])

To create the calculated column, press “Enter.”

Using a Measure

Navigate to the dataset in Power BI Desktop, and go to the “Modeling” tab in the ribbon. Next, click on “New Measure” in the Calculations group. Enter the formula using the MEDIAN function, and reference the column for which you want to calculate the median in Power BI. For instance:

MedianMeasure = MEDIAN(’Table’[Column])

Example:

From the image above, you can see the total sales for each customer. All you needed to do was add a visual to the report canvas (1 below), add the calculated column (2), and set the aggregation to MEDIAN (3).

From the image above, the answer is 270.27. The issue with this approach is that you’ll have to write a calculated column to get the answer. Yes, there is a time and place for a calculated column, but you don’t need to create a calculated column to get this answer.

The calculated column above is conceptually easy for anyone to understand. It’s easy to write. You can solve the problem one step at a time. You can see the interim results, and anyone with an Excel background can do it with very little understanding of DAX. Understanding the MEDIANX function is much harder. It’s harder until you realize that MEDIANX is doing the exact thing as this calculated column.

Here’s the MEDIANX formula.

Median Customer Sales Measure = MEDIANX(Customers,[Total Sales]).

How to Create a Visualization in Power BI?

Stage 1: Logging in to Power BI

• Log in to Power BI.
• Enter your email address and click the “Submit” button.

• • You are redirected to your Microsoft account.
  • Enter your password and click “Sign in“.

• You can choose whether to stay signed in.

• Once done, the Power BI home screen will open.

Stage 2: Creating a Data Set and Selecting the Data Set to Use in Your Chart

• Go to the left-side menu and click the “Create” button.
• Select “Paste or manually enter data“.

• We’ll use the sample data below for this example.

Application Channels	Initial Screening	Conduct Interviews	Employee Onboarding	Total Candidates
Social Media	Short Listed	Final Interview	Hired	32
Social Media	Short Listed	Final Interview	Not Hired	400
Social Media	Short Listed	Knocked Out		800
Social Media	Knocked Out			1100
Company Career Page	Short Listed	Final Interview	Hired	20
Company Career Page	Short Listed	Final Interview	Not Hired	250
Company Career Page	Short Listed	Knocked Out		500
Company Career Page	Knocked Out			900
Events	Short Listed	Final Interview	Hired	5
Events	Short Listed	Final Interview	Not Hired	100
Events	Short Listed	Knocked Out		200
Events	Knocked Out			350
Paper Media	Short Listed	Final Interview	Hired	3
Paper Media	Short Listed	Final Interview	Not Hired	80
Paper Media	Short Listed	Knocked Out		135
Paper Media	Knocked Out			700
Employee Referrals	Short Listed	Final Interview	Hired	10
Employee Referrals	Short Listed	Final Interview	Not Hired	70
Employee Referrals	Short Listed	Knocked Out		80
Employee Referrals	Knocked Out			110
Direct	Short Listed	Final Interview	Hired	25
Direct	Short Listed	Final Interview	Not Hired	150
Direct	Short Listed	Knocked Out		425
Direct	Knocked Out			600

• Paste the data table above into the “Power Query” window. After that, select the “Create a dataset only” option.

• Navigate to the left-side menu, and click on the “Data Hub” option. Power BI will populate the data set list. You’ll get an error message if no data set has been created.

• Click on the “Create report” dropdown.

• To add the Power BI Sankey Diagram Extension by ChartExpo, you’ll have to use an add-in or Power BI visual from AppSource. Navigate to the right side of the Power BI dashboard, and open the Power BI Visualizations panel. Next, click the ellipsis symbol (…) to import the Power BI Sankey Diagram extension by ChartExpo. In the following menu that opens, select the “Get more visuals” option.

• Enter “ChartExpo” in the highlighted search box. You’ll see the “Sankey Diagram for Power BI by ChartExpo” as shown in the image below.

• After that, click on the Sankey Diagram, and also click the highlighted “Add” button.

• Power BI will add the “Sankey Diagram for Power BI by ChartExpo” icon in the visualization panel.

• To draw a Sankey Diagram with ChartExpo’s Power BI extension, select the “Sankey Diagram for Power BI by ChartExpo” icon in the visualization panel. You’ll see a window similar to the one below opened in the report section of your dashboard.

• You’ll have to select the fields to use in the Sankey chart.
• Here’s the sequence to follow when selecting the fields:

• • Application Channels
  • Initial Screening
  • Conduct Interviews
  • Employee Onboarding
  • Total Candidates

• You’ll have to provide your email address or a ChartExpo License key. Add the key under the Visual section. After that, you’ll see the Sankey Chart displayed on your screen.

• You can add the top header text in the chart under the General section.

• After that, click on Visual to set the number and also enable the options.

• You can change the “Node Font Style.”

• You can change the “Level Font Style.”

• You can see the Level Labels as shown below:

• Here’s how to change the Nodes color.

• You’ve seen “How to change Level 1 color.” You can follow the same steps to change the Nodes color of other levels.

• Here’s the final look of the HR Dashboard in Power BI using ChartExpo after changing the all-nodes color.

Insights

• Social Media is the primary application channel with the highest number of candidates (1100).
• Company Career Page has the second-highest number of candidates (900).
• Paper Media and Events have comparatively lower candidate counts of 700 and 350, respectively.
• Direct application has 600 candidates.

What is the Best Practice for Using Power BI Median?

• Understand the Data Distribution: You need to have a good grasp of your data distribution. Assess whether it is skewed, contains outliers, or follows a normal distribution.
• Communicate Context: Always provide the context about what the median values represent and how they were calculated. This helps stakeholders understand the significance of the median in the data analysis.
• Handle Missing Data Appropriately: Address null (or missing) values in the dataset before calculating the median. You may opt to exclude, impute, or handle missing values in a way that aligns with your analysis goals.
• Use Median Alongside Other Measures: The median should be used alongside other statistical measures like mean, mode, and quartiles to gain a comprehensive understanding of the dataset’s characteristics.

What are the Advantages of Calculating the Median DAX?

• Robustness to Outliers: Unlike the mean, the median is less sensitive to outliers. It offers a more robust measure of central tendency, especially in datasets with extreme values (or skewed distributions).
• Reflects Central Tendency: The median accurately reflects the central tendency of the data distribution, especially in cases where the mean might be influenced by outliers. It offers a clearer picture of the typical value (or midpoint) of the dataset.
• Ease of Interpretation: The median is easy to interpret and understand. The calculation directly identifies the middle value of a dataset, and it also provides clear insights into the data distribution.
• Suitability for Non-Numeric Data: Unlike the mean which is only applied to numeric data, the median can be applied to both numeric and non-numeric data types like text or dates. This versatility makes it applicable across multiple datasets and analysis scenarios.

FAQs

What is the DAX function for the median?

The DAX function for calculating the median in Power BI is MEDIAN().

Is there a function for the median?

Yes, in Power BI and DAX, the function for calculating the median is MEDIAN(). It computes the median value of a column (or expression) in a dataset.

How do you insert a median function?

To insert a median function in Power BI, use the formula MEDIAN(). After that, use the column or expression you want to calculate the median within the formula.

Wrap Up

DAX median calculates the middle value of a dataset. It helps in the robust analysis of data. With the DAX median, you can get a reliable measure of central tendency less influenced by outliers, and that aids in accurate data interpretation and decision-making.

To get the most out of the DAX median calculation, you should always validate your results. And that involves checking with other tools or manual calculations. Validating your results helps you ascertain that the median values align with expectations.

Aside from calculating the DAX median, this guide also reveals how to create an attractive dashboard in Power.

Now you know how to find out median, what data will you be analyzing using the Sankey Chart in Power BI?