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