Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
StephenK
Resolver I
Resolver I

Fixing Average Aggregation

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

1A75%
1B50%
2A100%
3A95%
3B48%
3C25%

 

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!

 

2 REPLIES 2
wdx223_Daniel
Super User
Super User

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

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)

screenshot1.png

 

 

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

 

Screenshot 2 based on measure without calculated table.

 

screenshot2.png

 

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.

 

 

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors