how to get total amount excluding specific data

[shahidalaila23]

I need to get net amt for loc_group with loc_group 1034 total amt is excluding the amt that was send to other loc_group. 

the situation as below: 

below table represent loc_group and supp_code total amt. Only 1034 will buy item c from supp_code S-03 and will send 1 to other loc_group.

This is purchase table

and this is transfer table

I have both table in power BI

below is the expected outcome: 

so far I cannot seperate the amt from 1034 in powerBI 

thank you;

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your data model looks like, but please check the below picture and the attached pbix file.

Amount total expected result: =
VAR _expected =
    SUM ( Purchase[amt] )
        - CALCULATE (
            SUM ( Purchase[amt] ),
            FILTER ( ALL ( Purchase ), Purchase[supp_code] = "1034" )
        )
RETURN
    IF (
        HASONEVALUE ( loc_group[loc_group] ),
        SWITCH (
            SELECTEDVALUE ( loc_group[loc_group] ),
            "1034", _expected,
            SUM ( Purchase[amt] )
        ),
        _expected
    )