Calculating average

[PC2022]

I need to calculate EvalDesignM average per phase excluding zero scores. EvalDesignM include 3 phases and they are weighted differently, therefore multipliying each phase by 0.2, 0.25 and 0.25. EvalDesignM is weighted separately as well, therefore multiplying the return by 0.25. I am getting -39.29 as return but it should be between positive 0-3, as my scores are between 1-3:

EvalDesignM =

VAR ZeroAdministration = CALCULATE(COUNTROWS(crb87_consultantevaluation), crb87_consultantevaluation[crb87_proactivelyensuredunderstandingoftasksd] = 0, crb87_consultantevaluation[crb87_invoicesandprogressreportswereproperlyd] = 0, crb87_consultantevaluation[crb87_respondedtothecountyinatimelyd] = 0)

VAR ZeroIssuesResources = CALCULATE(COUNTROWS(crb87_consultantevaluation), crb87_consultantevaluation[crb87_providedeffectivesolutionstoidentifiedd] = 0, crb87_consultantevaluation[crb87_effectivelymanagedresourcesincludingsubd] = 0, crb87_consultantevaluation[crb87_effectivelyfacilitatedpermittingactivitiesde] = 0, crb87_consultantevaluation[crb87_effectivelyparticipatedinutilitycoordinationd] = 0)

VAR ZeroCommunication = CALCULATE(COUNTROWS(crb87_consultantevaluation), crb87_consultantevaluation[crb87_scheduledconductedanddocumentedd] = 0, crb87_consultantevaluation[crb87_communicatedanynecessarydesignd] = 0, crb87_consultantevaluation[crb87_effectivelytrackedandmonitoredd] = 0)

VAR ZeroExecution = CALCULATE(COUNTROWS(crb87_consultantevaluation), crb87_consultantevaluation[crb87_effectivelymanagedthebudgetd] = 0, crb87_consultantevaluation[crb87_designsweredoneinaccordanced] = 0, crb87_consultantevaluation[crb87_successfullymetthescoped] = 0)

RETURN

(SUMX(crb87_consultantevaluation,

 ((((crb87_consultantevaluation[crb87_proactivelyensuredunderstandingoftasksd] + crb87_consultantevaluation[crb87_invoicesandprogressreportswereproperlyd] + crb87_consultantevaluation[crb87_respondedtothecountyinatimelyd])) / 3 ) - ZeroAdministration) * 0.2 +

(((crb87_consultantevaluation[crb87_providedeffectivesolutionstoidentifiedd] + crb87_consultantevaluation[crb87_effectivelymanagedresourcesincludingsubd] + crb87_consultantevaluation[crb87_effectivelyfacilitatedpermittingactivitiesde] + crb87_consultantevaluation[crb87_effectivelyparticipatedinutilitycoordinationd]) / 4 ) - ZeroIssuesResources) * 0.25  +

(((crb87_consultantevaluation[crb87_scheduledconductedanddocumentedd] + crb87_consultantevaluation[crb87_communicatedanynecessarydesignd] + crb87_consultantevaluation[crb87_effectivelytrackedandmonitoredd] )) / 3 - ZeroCommunication)  * 0.25 +

(((crb87_consultantevaluation[crb87_effectivelymanagedthebudgetd] + crb87_consultantevaluation[crb87_designsweredoneinaccordanced] + crb87_consultantevaluation[crb87_successfullymetthescoped])) - ZeroExecution)  * 0.3  )) * 0.25

[v-hashadapu · Accepted solution]

Hi , Thank you for reaching out to the Microsoft Community Forum.

Please try this:
EvalDesignM =
VAR Admin =
AVERAGEX(
FILTER(
crb87_consultantevaluation,
crb87_consultantevaluation[crb87_proactivelyensuredunderstandingoftasksd] > 0 &&
crb87_consultantevaluation[crb87_invoicesandprogressreportswereproperlyd] > 0 &&
crb87_consultantevaluation[crb87_respondedtothecountyinatimelyd] > 0
),
DIVIDE(
crb87_consultantevaluation[crb87_proactivelyensuredunderstandingoftasksd] +
crb87_consultantevaluation[crb87_invoicesandprogressreportswereproperlyd] +
crb87_consultantevaluation[crb87_respondedtothecountyinatimelyd],
3
)
)
VAR Resources =
AVERAGEX(
FILTER(
crb87_consultantevaluation,
crb87_consultantevaluation[crb87_providedeffectivesolutionstoidentifiedd] > 0 &&
crb87_consultantevaluation[crb87_effectivelymanagedresourcesincludingsubd] > 0 &&
crb87_consultantevaluation[crb87_effectivelyfacilitatedpermittingactivitiesde] > 0 &&
crb87_consultantevaluation[crb87_effectivelyparticipatedinutilitycoordinationd] > 0
),
DIVIDE(
crb87_consultantevaluation[crb87_providedeffectivesolutionstoidentifiedd] +
crb87_consultantevaluation[crb87_effectivelymanagedresourcesincludingsubd] +
crb87_consultantevaluation[crb87_effectivelyfacilitatedpermittingactivitiesde] +
crb87_consultantevaluation[crb87_effectivelyparticipatedinutilitycoordinationd],
4
)
)
VAR Communication =
AVERAGEX(
FILTER(
crb87_consultantevaluation,
crb87_consultantevaluation[crb87_scheduledconductedanddocumentedd] > 0 &&
crb87_consultantevaluation[crb87_communicatedanynecessarydesignd] > 0 &&
crb87_consultantevaluation[crb87_effectivelytrackedandmonitoredd] > 0
),
DIVIDE(
crb87_consultantevaluation[crb87_scheduledconductedanddocumentedd] +
crb87_consultantevaluation[crb87_communicatedanynecessarydesignd] +
crb87_consultantevaluation[crb87_effectivelytrackedandmonitoredd],
3
)
)
VAR Execution =
AVERAGEX(
FILTER(
crb87_consultantevaluation,
crb87_consultantevaluation[crb87_effectivelymanagedthebudgetd] > 0 &&
crb87_consultantevaluation[crb87_designsweredoneinaccordanced] > 0 &&
crb87_consultantevaluation[crb87_successfullymetthescoped] > 0
),
DIVIDE(
crb87_consultantevaluation[crb87_effectivelymanagedthebudgetd] +
crb87_consultantevaluation[crb87_designsweredoneinaccordanced] +
crb87_consultantevaluation[crb87_successfullymetthescoped],
3
)
)
RETURN
(Admin * 0.2 + Resources * 0.25 + Communication * 0.25 + Execution * 0.3) * 0.25

*some changes maybe needed

If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[PC2022]

I am checking to see if the suggested DAX works, thank you.

I also need to calculate weight of the 3 columns separately and then calculate weight of the sum of those 3. Not sure what i am doing wrong:

EvalDesignSch =

(SWITCH(TRUE(),

crb87_consultantevaluation[crb87_timelinessofprojectphasesubmittalsd] <> 0, AVERAGE([crb87_timelinessofprojectphasesubmittalsd]) * 0.6,

crb87_consultantevaluation[crb87_timelysubmittalofprogressreportsandschedud] <> 0, AVERAGE(crb87_consultantevaluation[crb87_timelysubmittalofprogressreportsandschedud]) * 0.2,

crb87_consultantevaluation[crb87_timelinessofprojectphasedeliverablesandord] <> 0,  AVERAGE(crb87_consultantevaluation[crb87_timelinessofprojectphasedeliverablesandord]) * 0.2)) * 0.25

[lbendlin]

Most likely you will first need to unpivot your data to bring it into a usable shape.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...