Forum Discussion
Average
The columns that I have as raw data are (a) KPIs, (b) Indicator, (c) Weightage, (d) Past Performance, (e) Target, (f) Actual Result, (g) Assessee, and (h) Assessor. The different tables are later clubbed in “Table All” within Power BI with additional columns like (i) Age Bracket, (ii) Designation, (iii) Department, (iv) Gender, (v) Job Cadre, and (vi) Region.
A new column is used to calculate “Score”.
Score = IF('All'[Indicator]="Positive",'All'[Actual]/'All'[Target]*'All'[Weightage]*100, 'All'[Past Performance]/'All'[Target]*'All'[Weightage]*100)
I have used a separate table for my measures.
I need average score of each KPI for which i
Average Score = AVERAGE('All'[Score])
In order to get the average of all employees as a sum I have tried a couple of measures, but both aren’t working.
Sum of Average = AVERAGEX(VALUES('All'[Assessee]), [Average Score])
Weighted Score = AVERAGEX(DISTINCT(SELECTCOLUMNS('All', "Status", 'All'[Status], "Average Score", [Average Score])), [Average Score])
Most importantly, KPIs are appearing with exact average score irrespective of the slicers being used.
Below is the snapshot of the report.
oh, sorry, I forgot something.
how about this:
Sum of Average = AVERAGEX(VALUES('All'[Assessee]), CALCULATE(SUM('All'[Score])))
Really appreciated @lamnvt.
Your measure is much simpler and most importantly is giving the desired result.
Just minutes ago I got the solution by using the following measure.
Average =VAR AvgScore = AVERAGE('All'[Score])RETURNIF(HASONEVALUE('All'[KPIs]),AvgScore,SUMX(VALUES('All'[KPIs]), AvgScore))
4 Replies
hi,
you can try the following:
Sum of Average = AVERAGEX(VALUES('All'[Assessee]), 'All'[Score])