Forum Discussion

Steph83's avatar
Steph83
Frequent Visitor
3 years ago
Solved

How to calculate average

 

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

  • tamerj1's avatar
    tamerj1
    3 years ago

    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

4 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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. 

    • Steph83's avatar
      Steph83
      Frequent Visitor
      3 years ago

      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)

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        3 years ago

        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