devide by count of duplicate numbers

[Evandam]

Hi,

i need some help with a calculation i would like to perform.

invoice number	sub number	invoice total amount	sub amount
1389	101	7,425	7,388
1389	201	7,425	37
1340	111	9,500	5,000
1340	113	9,500	4,500

when i make a visual and insert the invoice number and invoice amount as value, the value is summarized so the outcome is like this:

invoice number	invoice amount
1389	(7.425*2) = 14,850
1340	(9,500*2_ = 20,000

As you can imagine this is not correct.

i needs to be: 

invoice number	invoice amount
1389	7,425
1340	9,500

can this be done in query editor by sum the total amount per invoice number and than divde that by counting the amount of times each invoice number occurs? 

thanks in advance

[PaulDBrown · Accepted solution]

A) If you need this in Power Query, remove the sub number & sub amount columns, select the Invoice number column and remove the duplicate rows:

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "VcvBDQAhCETRXjjPAQQi1mLov40VY8x64Ye8zJwkGoNAwrJuhzXf1QhKXG4Paz9mXFMpG3Cuz8GrD+uPbTfzAw==",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [
        #"invoice number" = _t,
        #"sub number" = _t,
        #"invoice total amount" = _t,
        #"sub amount" = _t
      ]
  ),
  #"Replaced Value" = Table.ReplaceValue(
    Source,
    ",",
    ".",
    Replacer.ReplaceText,
    {"invoice total amount", "sub amount"}
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    #"Replaced Value",
    {{"invoice total amount", type number}, {"sub amount", type number}}
  ),
  #"Removed Other Columns" = Table.SelectColumns(
    #"Changed Type",
    {"invoice number", "invoice total amount"}
  ),
  #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns", {"invoice number"})
in
  #"Removed Duplicates"

B) If you need this as a measure, use a simple AVERAGE function:

Average = 
AVERAGE('Table'[invoice total amount])

[Fowmy]

@Evandam 

Right click on the Invoice Amount and Choose MAximum 

or

Create a new measure to get the maximum value as follows:

Invoice Amount Max = MAX('TableName'[Invoice Amount])