Forum Discussion
Sum the value and find max
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
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
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] )
4 Replies
- Jihwan_Kim
Super User
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
CNENFRNLCommunity Champion
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] )
v-henryk-mstfCommunity Support
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 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.