Fixing Average Aggregation

Hey wdx223_Daniel thanks for the response.

Unfortunately this did not fix my issue. Please see additional context below.

Visual based on calculated table using score measure: AVERAGEX(VALUES('Fact'),SUM('Fact'[Score])

Screenshot 1 (based on measure in calculated table)

Visual based on DAX measure: AVERAGEX(VALUES('Fact'),SUM('Fact'[Score])

Screenshot 2 based on measure without calculated table.

You can see that the totals come out differently.

There are two row dimensions. "Location" and "Department". A location will have many departments. 

The screenshots are both showing the averages drilled to the Department level.

The measure is aggregating score values for individual "sales people" up to the location level.

Notice that the department level scores are exactly the same. However, when you drill up to Location or look at the grand total at the bottom, the scores are different. Manually review indicates that screenshot 1 built with a calculated table has the correct aggregation.

What the measure should be doing is averaging the scores for all sales people by department. Then averaging the department scores by location. Then averaging the location scores for the grand total.

It is only doing this correctly when the measure is first put in a calculated table. You might ask, ok so just use the calculated table measure. I cannot because i need to be able to dynamically remove sales people from the aggregation via slicer to determine score impacts, so I have to build this with a measure outside a calculated table.

Hope that helps clarify.