Calculate the Average using a Dimension column

AndyDDC — Thu, 23 Feb 2023 15:12:34 GMT

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.

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.

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

Re: Calculate the Average using a Dimension column

tamerj1 — Thu, 23 Feb 2023 15:18:09 GMT

Hi AndyDDC
Please use

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

Re: Calculate the Average using a Dimension column

AndyDDC — Thu, 23 Feb 2023 17:54:59 GMT

Brilliant, thank you!

topic Calculate the Average using a Dimension column in DAX Commands and Tips

Calculate the Average using a Dimension column

Re: Calculate the Average using a Dimension column

Re: Calculate the Average using a Dimension column