How to calculate with DAX, Average of repeated points within a group of categories?

[TFernandes_2022]

Hello guys

How can I calculate the average of repeated points within a group of categories, column (Pontuação_New)?

In the column group ("Categoria" | "Tipo" | "Dia"), when there are repeated values ​​in the "PTS MAX" column, calculate the average of the "Pontuação" column, when there is no repeated value use the "Pontuação" column.

My dataset

let
Fonte = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZfBbuIwEIZfxcq5B+KULBwpFe1KsFqtqh4WVWhIrOJuYnftpIe+0D7IvtiasDjFjh0nPdTlMPY3Hv5/xmy30brOQKIllxWghYD6hUdX0aKEnAv1IUYPpHxtPtxSUP/t8J0RvDuHxlO1zKKnq230AFLSWqJ7KGBPvYDLUPfhiVqmzeFLKGkB6AaE4BI9UkIFeBFdG9ygWC1JA1oTzkDkHH0nUgI7+ClWtBNxrFM87Cp4yFXOwXGqlnkD+kGzJrcliDcoDhzlBG143UNz7nIjJ2pJT9ULExoOF9oF5LqBrJRmgHG0os+1qgUHdFuDqIgX5drkBB61hwdIAgdL4hyJgyWBh6kbG+pOtUfvSaGqAGhFBAOWq6oX+/p3TYT6QwuWC8iJFxxygDORL9rOC3XEktcVZUpgd5z9/QPFG5HHY0Xv5fs2O/lp63KvTi2iT6cGo/1SR90Rj73jhU/mPQ3ZwrkasnH4XBv9q4Q9KZQEH2kOBdrU7+/02CnWtPRfzLfRyU2094OdEtY8Ozj4c0bBnzHKxzxOIrqDvaBEXWDNX9XXv+HiGdhHcGJqtXPHzohv5dqgTrXdQHUgSgQntd8IXpacyexA2C9SINXRvoHMaElYxb0ZDDjHnVd8Gst+HVtol47N01uT9AnKQvgFZYBUZY9tr7/lWBhfy+mCpKNka2GHydZMZKIbfGh/sBII6w8GeKb7g3dW20V2z+rENTr++6t9H/2E7ACCggyxZcc2J/Nad6Mho8Qih48Sg992oWCThHXdxHgHTVqvD6wuHl5dPShn0flVNKq8wye1mUBr26CXukUOeKmbSNwaJawdhbzUDchMW2V0Nxo3RI08pto+YU0h4AFvClcbpLOaG5AVCf2BbQRf/sBOQ2VqI4NlaiZw8aBUdXz6Bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Nome = _t, Categoria = _t, Tipo = _t, Dia = _t, ID_Entidade = _t, ID_Cabeçalho = _t, #"PTS MAX" = _t, Pontuação = _t]),
#"Tipo Alterado" = Table.TransformColumnTypes(Fonte,{{"Nome", type text}, {"Categoria", type text}, {"Tipo", type text}, {"Dia", type text}, {"ID_Entidade", type text}, {"ID_Cabeçalho", type text}, {"PTS MAX", Int64.Type}, {"Pontuação", Int64.Type}})
in
#"Tipo Alterado"

[v-xinruzhu-msft · Accepted solution]

Hi @TFernandes_2022  ,

You can create a new column and input the following code

Pontuação_New = CALCULATE(AVERAGE(Query1[Pontuação]),Query1[PTS MAX]=EARLIER(Query1[PTS MAX])&&Query1[Categoria]=EARLIER(Query1[Categoria])&&Query1[Tipo]=EARLIER(Query1[Tipo])&&Query1[Dia]=EARLIER(Query1[Dia]),ALL(Query1))

The output is as follows

^{Best Regards,}

^{Yolo Zhu}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[amitchandak]

@TFernandes_2022 . You need a column like that, But your results are different in screenshot

averagex(filter(Table, [Categoria] = earlier( [Categoria]) && [Tipo] = earlier( [Categoria]) && [Dia] =earlier( [Dia])), [PTS MAX])

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.