Line chart for average performance metric by category using DAX — PowerBI

Friends, let us understand the DAX formula for finding the average performance metric with a simple example.

Suppose your objective is to find the average performance scores for females and males using the gender column of data and plot them using a line chart. For this consider the below sample data available in an excel file.

Below are the findings for the average performance score in excel using the pivot table —

Our objective is to plot below line chart —

The formula to find average performance scores is —

Average performance score of females in 2015 = Sum of performance scores for females in 2015 / Count of total females in 2015

Similarly, the formula applies for males —

Average performance score of males in 2015 = Sum of performance scores for males in 2015 / Count of total males in 2015

Now, to plot the above line chart in Power-BI, initially, you need to create two new measures for average performance scores for females and males.

Follow the below steps —

Step1 — Import the data to PowerBI —

Step2 — Create two ‘New Measures’ from the ‘Modeling’ option using the above formulas which in DAX (Data Analysis Expressions) can be written in the format —

Average Performance Score Female = CALCULATE(AVERAGE(data[Performance Score]), Filter(data,data[Gender]=”Female”))

And for males,

Average Performance Score Male = CALCULATE(AVERAGE(data[Performance Score]),Filter(data,data[Gender]=”Male”))

Step3 — Select ‘Line chart’ from the ‘Visualizations’ section and make the highlighted selections in ‘Axis’ and ‘Values’. Make a note to select the ‘Don’t summarize’ for the ‘ Year’ column by clicking on it as it is basically a numeric column so by default it will be summarized to ‘ Sum’ —

Step4 — Do some formatting from the ‘Format’ section like modifying title, font, font-size, legend formatting, making data labels ‘On’ and so on, and then you are done! 😊


Thanks for reading ❤

For any suggestions or queries, leave your comments below and follow for updates.

If you liked the article, please hit the 👏 icon to support it. This will help other Medium users find it. Share it, so that others can read it!

Happy Learning! 😊




Data Analyst at Infosys Ltd., AI Engineer, MSc. Statistics SPPU.

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

For States Across the Country, Data Powers COVID-19 Response Efforts

The Confusing Pathway Of Machine Learning And Data Science

A live AI web-server with Intel NCS and a RaspberryPi

Optimising Water Consumption and Predicting Plant Health using Multispectral Drone Data.

To be a master in Data science

5 Reasons Why You Should Use MS Power BI If You’re Still Using MS Excel

Does that viz really show what you think it shows?

Why accurate real-time data matters

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Priyanka Dandale

Priyanka Dandale

Data Analyst at Infosys Ltd., AI Engineer, MSc. Statistics SPPU.

More from Medium

Problem data

Prediction: Excel Users will need to learn data science to remain relevant

Linear Regression with real world application

Tips for an Effective Data Visualization