Forum Discussion

kirvis's avatar
kirvis
Icon for Helper I rankHelper I
4 years ago
Solved

Determine invoice category based on highest-value line item

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 numberLine itemCategoryValueDesired result
11Bordeaux110Bordeaux
12Languedoc15Bordeaux
13Alsace55Bordeaux
21Bordeaux150Alsace
22Alsace300Alsace

 

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

Need Help
  • smpa01's avatar
    smpa01
    4 years ago

    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 )

     

     

4 Replies

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      kirvis
      Icon for Helper I rankHelper I
      4 years ago

      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's avatar
        smpa01
        Icon for Community Champion rankCommunity Champion
        4 years ago

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

  • kirvis's avatar
    kirvis
    Icon for Helper I rankHelper I
    4 years ago

    Wow, this truly is like magic.

     

    Thanks for the quick reply and for the elegant solution!