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.

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! 😊

____________________________________________________________

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! 😊

--

--

--

More from Priyanka Dandale

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

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

Priyanka Dandale

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