topic Re: How to calculate average in DAX Commands and Tips

How to calculate average

Steph83 — Sun, 23 Oct 2022 14:27:19 GMT

Good day,

I closed my topic too quickly

Here was my demand:

How to calculate average age of a group of persons in XX years?
I have a group of 300 people ("Artists") for whom I have their date of birth ("date of birth").

I would like to know the average age of this group on a specific date that I would select in a dashboard filter.

In fact my data base gathered all persons, but all are not available all the time. So I need to know, among the persons who are there, at a specific date, the average age of the persons

John proposed the following:

Average age =
VAR ReferenceDate =
SELECTEDVALUE ( 'Slicer date'[Date] )
VAR Result =
AVERAGEX ( Artists, DATEDIFF ( Artists[Date of birth], ReferenceDate, YEAR ) )
RETURN
Result
where 'Slicer date'[Date] is the column you are using on the slicer.

Something is missing. I have well the average of the list of persons at a selected date, however, it gives the average of all the persons whatever the filter applied on the page (gender, above 30....). How can I include the selected filters in the formula so as to calculate the average on the selected persons only?

Thanks in advance for you support

Stephane

Re: How to calculate average

tamerj1 — Sun, 23 Oct 2022 15:27:22 GMT

Hi Steph83

do you mean you have a column in your rmtable that specifies the date when the record was added? And you want the date slicer selected date to filter all thoses records which were added on or before the selected date?

on the other hand I believe the provided measure shall not have any issue with your page and report filters. It should work with these filters just fine.

Re: How to calculate average

Steph83 — Sun, 23 Oct 2022 17:31:49 GMT

Hi tamerj1 ,

I have 2 tables:

1 - One table which lists the people month by month

2 - One table which contains all the data of the persons

Both table are linked by the person (1 to multiple)

Re: How to calculate average

tamerj1 — Mon, 24 Oct 2022 09:43:08 GMT

Hi Steph83
I hope the month column is Date formatted (I called [Date Added] in my code)

Average age = VAR ReferenceDate = SELECTEDVALUE ( 'Slicer date'[Date] ) VAR Result = AVERAGEX ( CALCULATETABLE ( Artists, People[Date Added] <= ReferenceDate, CROSSFILTER ( Artists[Person ID], People[Person ID], BOTH ) ), DATEDIFF ( Artists[Date of birth], ReferenceDate, YEAR ) ) RETURN Result

Re: How to calculate average

Steph83 — Mon, 24 Oct 2022 12:28:46 GMT

Hi tamerj1 ,

It works perfectly, thanks for your support!

Stephane