cancel
Showing results for 
Search instead for 
Did you mean: 
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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors