Determine invoice category based on highest-value line item

[kirvis]

Hi all,

I am working on a project to assess the sales at our company, based on the invoices. I have a dataset at line item level, and want to assign a category to each invoice number based on the line item with the highest value.

Example data:

Invoice number	Line item	Category	Value	Desired result
1	1	Bordeaux	110	Bordeaux
1	2	Languedoc	15	Bordeaux
1	3	Alsace	55	Bordeaux
2	1	Bordeaux	150	Alsace
2	2	Alsace	300	Alsace

So basically, the steps are as follows:

1. Determine the MAX line item within the group of an invoice number
2. Assign the category of that line item bach to all other line items with that invoice number

Should be quite simple I guess, but I am struggling with the implementation at the moment.

Does anyone know how to do this?

Thanks!

Kirvis

[kirvis]

Wow, this truly is like magic.

Thanks for the quick reply and for the elegant solution!

[smpa01 · Accepted solution]

@kirvis  with PQ

List.Max(
      Table.SelectRows(
        CT,
        (r) =>
          r[Invoice number]
            = [Invoice number] and r[Value]
            = List.Max(Table.SelectRows(CT, (q) => q[Invoice number] = [Invoice number])[Value])
      )[Category]
    )

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMlTSAWOn/KKU1MTSChDX0EApVgciZQTEPol56aWpKfnJIDlTuJQxEDvmFCcmpwIZphBxIyymmRrApYyQtRgbACViAQ==",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [#"Invoice number" = _t, #"Line item" = _t, Category = _t, Value = _t]
  ),
  CT = Table.TransformColumnTypes(
    Source,
    {
      {"Invoice number", Int64.Type},
      {"Line item", Int64.Type},
      {"Category", type text},
      {"Value", Int64.Type}
    }
  ),
  #"Added Custom" = Table.AddColumn(CT, "maxCAT", each List.Max(
      Table.SelectRows(
        CT,
        (r) =>
          r[Invoice number]
            = [Invoice number] and r[Value]
            = List.Max(Table.SelectRows(CT, (q) => q[Invoice number] = [Invoice number])[Value])
      )[Category]
    ))
in
  #"Added Custom"

with DAX

_maxCAT =
VAR _partition =
    ALLEXCEPT ( 'Table', 'Table'[Invoice number] )
VAR _val =
    CALCULATE ( MAX ( 'Table'[Value] ), _partition )
RETURN
    CALCULATE ( MAX ( 'Table'[Category] ), 'Table'[Value] = _val, _partition )

[kirvis]

@smpa01 could it be that the query is very slow when working with several thousand lines? When I try your query on a few lines, it works fine, but as soon as I try it on the complete dataset (a few thousand lines) then it takes forever (up to 5 seconds per line).

Any advice on how to solve this?

[smpa01]

@kirvis  I am guessing you are asking about PQ and not DAX. If yes, please follow the the known hacks described here