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

[PBIuser73]

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.

[Sahir_Maharaj]

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.

[PBIuser73]

Hi.

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

Frank

[Anonymous · Accepted solution]

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.