topic Summarize values on multiple rows based on value in one of the rows in DAX Commands and Tips

Summarize values on multiple rows based on value in one of the rows

PBIuser73 — Fri, 23 Aug 2024 10:33:47 GMT

Hi.

I have a table like this:

Orderno	Articlecode	Amount
10001	246211	100
10001	MDAHELG	400
10002	299093	400
10002	MDAHELG	400
10002	365256	600
10003	299095	300
10004	366494	500
10004	MDAHELG	400

I want to summarize the amounts on only a few of this rows.

I want to summarize the rows that have this Articlecodes (hardcode is ok):

* 246211, 299093 and 299095

BUT i also want to add the sum for the rows that have the code MDAHELG and have the same orderno as the rows with the same Articlecodes as above) 246211, 299093 and 299095)

Like this:

Orderno	Articlecode	Amount
10001	246211	100
10001	MDAHELG	400
10002	299093	400
10002	MDAHELG	400
10002	365256	600
10003	299095	300
10004	366494	500
10004	MDAHELG	400

The Articlecode 365256 and 366494 are not going to be summarized and either MDAHELG that are related to Orderno 10004 with Articlecode 366494.

The sum should be 1600.

Re: Summarize values on multiple rows based on value in one of the rows

Sahir_Maharaj — Fri, 23 Aug 2024 10:53:41 GMT

Hello PBIuser73,

Can you please try the following:

TotalAmount = CALCULATE( SUM('Table'[Amount]), 'Table'[Articlecode] IN {"246211", "299093", "299095", "MDAHELG"}, 'Table'[Orderno] IN CALCULATETABLE( VALUES('Table'[Orderno]), 'Table'[Articlecode] IN {"246211", "299093", "299095"} ) )

Hope this helps.

Re: Summarize values on multiple rows based on value in one of the rows

PBIuser73 — Mon, 26 Aug 2024 06:31:21 GMT

Hi.

It did not work. The amount got a lot bigger than expected.

Frank

Re: Summarize values on multiple rows based on value in one of the rows

Anonymous — Mon, 26 Aug 2024 08:17:26 GMT

Hi PBIuser73 ,

I think you can try this code to create a measure.

Measure = VAR _LIST = { "246211", "299093", "299095" } VAR _ArticlecodeList = { "246211", "299093", "299095", "MDAHELG" } VAR _OrdernoList = CALCULATETABLE ( VALUES ( 'Table'[Orderno] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Articlecode] IN _LIST ) ) RETURN CALCULATE ( SUM ( 'Table'[Amount] ), FILTER ( 'Table', 'Table'[Orderno] IN _OrdernoList && 'Table'[Articlecode] IN _ArticlecodeList ) )

Result is as below.

Best Regards,
Rico Zhou

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