Forum Discussion

mellolu's avatar
mellolu
Frequent Visitor
5 years ago
Solved

AVERAGE AGE with repeted values

Hi everybody, how can I calculate average age when in a table the same person appears more than once?
 
Need Help
  • Icey's avatar
    Icey
    5 years ago

    Hi mellolu ,

     

    Based on the measure AlB provided, you can also create a measure like so:

    Avg Age =
CALCULATE (
    AVERAGEX ( DISTINCT ( 'Table'[Name] ), CALCULATE ( MIN ( 'Table'[Age] ) ) ),
    ALLSELECTED ( 'Table' )
)

     

    Best regards

    Icey

     

    If this post helps, then consider Accepting it as the solution to help other members find it faster.

     

7 Replies

  • Icey's avatar
    Icey
    Community Support
    5 years ago

    Hi mellolu ,

     

    Based on the measure AlB provided, you can also create a measure like so:

    Avg Age =
CALCULATE (
    AVERAGEX ( DISTINCT ( 'Table'[Name] ), CALCULATE ( MIN ( 'Table'[Age] ) ) ),
    ALLSELECTED ( 'Table' )
)

     

    Best regards

    Icey

     

    If this post helps, then consider Accepting it as the solution to help other members find it faster.

     

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    Hi mellolu 

    If you do not show a sample of your data and the expected result, it is difficult to provide an accurate answer.

    Try this measure in a card visual:

    AvgAge =
AVERAGEX ( DISTINCT ( [PersonID] ), CALCULATE ( MIN ( Table1[Age] ) ) )

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

     

    • mellolu's avatar
      mellolu
      Frequent Visitor
      5 years ago

      Table1

      Name       Age

      Name1     20

      Name2    15

      Name1    20

      Name3    30

       

      Age Average should be 21.67, but in my project it is 21,25 (because one student appears more than once)

      I don't know if I was clear...

      • AlB's avatar
        AlB
        Community Champion
        5 years ago

        mellolu 

        You were clear. that is exactly what the code I suggested earlier does. I will repeat it here:

        AvgAge = 
AVERAGEX ( DISTINCT ( Table1[Name] ), CALCULATE ( MIN ( Table1[Age] ) ) )

         Another option would be to eliminate the duplicates in the query editor, so that you do not have to worry about them when calculating the average.  See it all at work in the attached file.

         

        Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

        Contact me privately for support with any larger-scale BI needs, tutoring, etc.

        Cheers 

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    roughly answering the generic question, you should make use of the Table.Distinct and List.Average functions.

     

     

    • mellolu's avatar
      mellolu
      Frequent Visitor
      5 years ago

      Can  you explain me how to do that? I'm new on it..

      tks

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        copy and paste the following code into the advanced editor. Although it is not clear to me why you want to use an unfamiliar tool to do this type of calculation. Wouldn't excel be simpler?

         

        let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTTVU0lEyMlCK1YFwjYBcQ1M4F03WGMg1BnJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Age = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Name", type text}, {"Age", Int64.Type}}),
    #"Rimossi duplicati" = Table.Distinct(#"Modificato tipo", {"Name"}),
    Age = #"Rimossi duplicati"[Age],
    #"Media calcolata" = List.Average(Age)
in
    #"Media calcolata"