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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
AndyDDC
Most Valuable Professional
Most Valuable Professional

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:

AndyDDC_0-1677164636559.png

 

The Fact table is:

 

AndyDDC_1-1677164651916.png

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.

 

Average Answer = CALCULATE(AVERAGE(DimAnswer[AnswerID]),CROSSFILTER(DimAnswer[AnswerKey],FactAnswer[AnswerKey],BOTH))

 

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.

AndyDDC_2-1677164847423.png

 

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
tamerj1
Super User
Super User

Hi @AndyDDC 
Please use

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

View solution in original post

2 REPLIES 2
tamerj1
Super User
Super User

Hi @AndyDDC 
Please use

AverageMeasure =
AVERAGEX ( Fact, RELATED ( Dim[AnswerID] ) )
AndyDDC
Most Valuable Professional
Most Valuable Professional

Brilliant, thank you!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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