AVERAGE AGE with repeted values

[mellolu]

Hi everybody, how can I calculate average age when in a table the same person appears more than once?

[Icey · Accepted solution]

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.

[Anonymous]

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

[mellolu]

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

tks

[Anonymous]

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"

 

[AlB]

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]

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]

@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