cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Memorable Member

## Calculate the Average using a Dimension column

Hi,

I'm trying to calculate the Average of a dimension attribute based on a Fact table. The following are the tables (simplified, eg Category doesn't actually live in the Fact table but there to visualise what I'm trying to do)

The dimension table is:

The Fact table is:

What I'd like is to calculate the average AnswerID over all the rows in the Fact table, I've tried to create a measure but all it's doing is calculating the average over the distinct AnswerID values, not over each AnswerID that would be linked to the fact table.

But the result is incorrect as it's just calculating the average for each distinct AnswerID.  In the case of Category A, there are 7 rows in the fact table, when the AnswerID for each of the 7 rows is add up it totals 20.  So 20/7 = 2.8.  But what my measure is doing is just taking the unique AnswerIDs which would be 2,4,6, then dividing by the distinct number of AnswerID which is 3.  So 12/3 = 4.

Could anyone point me towards how I would do the Average by taking each row in the Fact table, getting the AnswerID and then doing the average over this?

Many thanks

1 ACCEPTED SOLUTION
Super User

Hi @AndyDDC

``````AverageMeasure =
AVERAGEX ( Fact, RELATED ( Dim[AnswerID] ) )``````
2 REPLIES 2
Super User

Hi @AndyDDC

``````AverageMeasure =
AVERAGEX ( Fact, RELATED ( Dim[AnswerID] ) )``````
Memorable Member

Brilliant, thank you!