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