Issue with Aggregating Scores Correctly in Power BI Matrix

[sg_compass]

Hello Power BI Community,

I'm encountering a problem with a measure in my Power BI report that I hope someone can help me resolve. The measure is designed to calculate the score for individual units by aggregating scores and then subtracting 5% for each instance of "KO". This calculation works correctly at the unit level. However, when aggregating these scores at a higher level in a matrix (e.g., region level), the measure incorrectly applies the 5% KO deduction to the total aggregated score of the region, leading to significantly incorrect negative values.

Here's the behavior I'm observing:

• At the unit level, the score is calculated correctly by adding up the scores and then applying the 5% deduction for each "KO".
• At the region level, instead of averaging the individual adjusted scores, the matrix aggregates all the scores and then applies the KO deductions cumulatively across the entire region.

The desired outcome is for the matrix to first apply the 5% KO deduction to each individual unit score and then average these adjusted scores to calculate the region's score. This approach would prevent the disproportionate impact of the KO deductions when viewed at the regional level.

Is there a DAX function or a modification to the measure that would allow the matrix to aggregate the individual scores correctly by applying deductions before averaging, rather than deducting after aggregating?

Thank you in advance for any assistance or insights you can provide!

Here is the measure im talking about:

[AndyEagleton · Accepted solution]

You just need to do the calculation at the unit level then average it.

AVERAGEX( VALUES(UnitKey) ,  Unit Level Measure Code )

In this expression the Unit Level Measure Code can be the above code pasted in (becasue it already has the CALCULATE to do a context transition) or measure reference to it e.g. [Final Score (Unit Level)].

[Greg_Deckler]

@sg_compass First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

Also, bless you for further validating my infamous -5 example that everyone hates. Yes people, it's real world.