cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## 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 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

1 ACCEPTED SOLUTION
Super User

@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}
}
),
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

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 )

Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
4 REPLIES 4
Helper I

Wow, this truly is like magic.

Thanks for the quick reply and for the elegant solution!

Super User

@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}
}
),
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

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 )

Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Helper I

@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?

Super User

Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors