Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to Solution.
Hi @AndyDDC
Please use
AverageMeasure =
AVERAGEX ( Fact, RELATED ( Dim[AnswerID] ) )
Hi @AndyDDC
Please use
AverageMeasure =
AVERAGEX ( Fact, RELATED ( Dim[AnswerID] ) )
Brilliant, thank you!
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
23 | |
10 | |
10 | |
9 | |
7 |