How to subtract one measure from another with different filters for each

[miss-ness]

Hello PBI wizards! Is anyone able to help me logic out this new measure?

I need to create a new measure (outlined at the very bottom of this post) that calculates the difference between the following two measures, but each measure is filtered for different criteria. The first needs to be filtered for all lines within the same table where the Origin Territory column equals the Destination Territory column, for a chosen Origin Site Code (another column within the same table). The second needs to be filtered for the reverse; all lines where Origin Territory does NOT equal Destination Territory, for a chosen Destination Site Code (another column within the same table).

Measure 1: Local Average Cost per Unit

DIVIDE(

    CALCULATE(SUM(Cost),

        Origin Territory = Destination Territory

    ),

    CALCULATE(SUM(Quantity), 

        Origin Territory = Destination Territory

    )

)

Measure 2: Out of Market Average Cost per Unit

DIVIDE(

    CALCULATE(SUM(Cost),

        Origin Territory <> Destination Territory

    ),

    CALCULATE(SUM(Quantity),

        Origin Territory <> Destination Territory

    )

)

New Measure = Measure 1 filtered for Origin Site Code XXXX minus Measure 2 filtered for Destination Site Code XXXX (XXXX represents the same site code for both measures as well as for the new measure)

Thank you so much to anyone & everyone for your time and efforts

[h21]

Hi @miss-ness 
It will be helpful if you share sample date, so I will be able to provide you with proper measure to be used. 

[miss-ness]

This is an example of a table all three measures would be based on. There are a number of other columns also in this table but are either irrelevant here or would be filtered for the same criteria for all three measures. Does this help contextualize?

(You can see there may be multiple sites belonging to a single territory.)

Date	Origin Site	Origin Territory	Dest. Site	Dest. Territory	Quantity
1/4/2021	ABCD	South	MNOP	North	1
1/17/2021	ABCD	South	QRST	North	2
2/8/2021	EFGH	West	UVWX	South	3
3/11/2021	IJKL	East	MNOP	North	2
3/17/2022	EFGH	West	UVWX	South	4

[amustafa]

Perhaps this might help...

New Measure = 
CALCULATE(
    [Local Average Cost per Unit],
    'Table'[Origin Site Code] = "XXXX",
    'Table'[Origin Territory] = 'Table'[Destination Territory]
)
-
CALCULATE(
    [Out of Market Average Cost per Unit],
    'Table'[Destination Site Code] = "XXXX",
    'Table'[Origin Territory] <> 'Table'[Destination Territory]
)

[miss-ness]

Hi @amustafa , thanks so much for your response. The issue with this solution is that I will need a more dynamic measure that calculates across all Destination Site Codes, not only XXXX.