Sum the value and find max

[Anonymous]

Hello All,

I have a table as below.

Order	Part	QTY	Year	Date	Buyer
A001	12345	120	2019	20190102	ABC
A001	12345	50	2019	20190212	ABC
A002	12345	70	2019	20190315	DEF
A001	12346	50	2019	20190115	ABC
A001	12346	50	2019	20190210	ABC
A003	12346	150	2019	20190312	XYZ
A003	12346	50	2019	20190210	XYZ

I would like to see the Part number and Buyer for the Max Quantity(sum of rows for the Part number if multiple rows).

In short it should be 

Part      Buyer

12345   ABC - AS that buyer has max qty 120+50

12346   XYZ  

Thanks

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please check the below for creating a measure.

Buyer Measure =
IF (
ISFILTERED ( 'Table'[Part] ),
MAXX (
FILTER (
SUMMARIZE ( 'Table', 'Table'[Buyer], "@qtytotal", SUM ( 'Table'[QTY] ) ),
[@qtytotal]
= MAXX (
GROUPBY (
'Table',
'Table'[Buyer],
"@qty", SUMX ( CURRENTGROUP (), 'Table'[QTY] )
),
[@qty]
)
),
'Table'[Buyer]
)
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

@CNENFRNL @Jihwan_Kim Thanks for your help.Both of them worked and its just that i had to learn few things before putting them to use.

[v-henryk-mstf]

Hi @Anonymous ,

For the solution provided by @Jihwan_Kim , I did a test. Can also get the desired result.

Best Regards,
Henry

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

[CNENFRNL · Accepted solution]

Buyer Max = 
MAXX( TOPN( 1, DISTINCT( Sales[Buyer] ), CALCULATE( SUM( Sales[QTY] ) ) ), Sales[Buyer] )

bonus

Buyer Min = 
MAXX( TOPN( 1, DISTINCT( Sales[Buyer] ), CALCULATE( SUM( Sales[QTY] ) ), 1 ), Sales[Buyer] )

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please check the below for creating a measure.

Buyer Measure =
IF (
ISFILTERED ( 'Table'[Part] ),
MAXX (
FILTER (
SUMMARIZE ( 'Table', 'Table'[Buyer], "@qtytotal", SUM ( 'Table'[QTY] ) ),
[@qtytotal]
= MAXX (
GROUPBY (
'Table',
'Table'[Buyer],
"@qty", SUMX ( CURRENTGROUP (), 'Table'[QTY] )
),
[@qty]
)
),
'Table'[Buyer]
)
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM