topic Re: Calculating average in DAX Commands and Tips

Calculating average

PC2022 — Wed, 16 Apr 2025 19:12:55 GMT

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

Re: Calculating average

lbendlin — Wed, 16 Apr 2025 21:15:05 GMT

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-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Calculating average

v-hashadapu — Thu, 17 Apr 2025 11:19:08 GMT

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.

Re: Calculating average

PC2022 — Thu, 17 Apr 2025 16:41:02 GMT

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