Average

[Atif]

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.

[Iamnvt · Accepted solution]

oh, sorry, I forgot something.

how about this:

Sum of Average = AVERAGEX(VALUES('All'[Assessee]), CALCULATE(SUM('All'[Score])))

[Iamnvt]

hi,

you can try the following:

Sum of Average = AVERAGEX(VALUES('All'[Assessee]), 'All'[Score])

[Atif]

Hi there @Iamnvt ,

Sum of Average = AVERAGEX(VALUES('All'[Assessee]), 'All'[Score]) can't be done.

The syntax is "table" and then "expression".

[Iamnvt · Accepted solution]

oh, sorry, I forgot something.

how about this:

Sum of Average = AVERAGEX(VALUES('All'[Assessee]), CALCULATE(SUM('All'[Score])))

[Atif · Accepted solution]

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])

RETURN

IF(HASONEVALUE('All'[KPIs]),

    AvgScore,

        SUMX(VALUES('All'[KPIs]), AvgScore))