Fixing Average Aggregation

[StephenK]

Hey all,

I'm having some trouble getting my averages to aggregate correctly in an explicit measure. At a risk of oversimplifying my question and the issue, here is an abbreviated version of the problem:

I have a fact table that looks roughly like the following:

Location           Department    Score

1	A	75%
1	B	50%
2	A	100%
3	A	95%
3	B	48%
3	C	25%

I have a calculated table that is averaging up the scores by location. 

When I through the score totals into a table visual with location as rows, the grand totals are calculating correctly.

The problem is a calculated table is not fully dynamic in the sense that it only refreshes when the power bi dataset is refreshed.

I need a dynamic measure, so I'm going with a straight dax measure instead of the calculated table.

However when I it this way, the grand total averages are not coming out the same as the calculated table (which I know to be correct).

My DAX measure is 

AVERAGE('Fact'[Score])

Note that the measure the calculated table is using is exactly the same, but the grand totals are different depending on if i'm using the column in the calculated table that is built on this measure, or if i'm just putting this measure straight into a visual.

I'm not sure why my grand totals are different. Sometimes they are off by a couple percentage points, sometimes less.

Any help is appreciated!

[wdx223_Daniel]

Averagex(values(fact[location]),average(fact[score]))

[StephenK]

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.