Performing a calculation on numbers from two unrelated tables

[Anonymous]

Hi!

I’m working with two datasets that don’t have a way of making a relation to each other. I need to subtract the number of crabapples from a specific store in the District dataset from the number of apples from the same specific store in the All Stores data set. If the number of apples is less than crabapples, then no subtraction is necessary.

I used UNION to join the two datasets, then used a big IF statement in a calculated column in the UNION table to figure out the correct subtraction. Then I went back to the original All Stores data set and used a RELATED function to get the new number of apples from the UNION table in a calculated column. And I got a circular dependency error.

Any ideas how I can do this subtraction?

Thanks so much!

All Stores Data Set

Store	Item	Units	New Units I'm looking for	Notes:
Uptown	Apples	7	7	No crabapples are from the Uptown store in the District data set
Uptown	Strawberries	9	9
Uptown	Grapes	4	4
Downtown	Apples	5	2	3 crabapples are from the Downtown store in the District data set
Downtown	Bananas	3	3
Downtown	Pears	10	10
Suburbs	Apples	1	0	1 crabapple is from the Suburb store in the District data set
Suburbs	Cantalopes	6	6
Suburbs	Pineapple	2	2
Rural	Apples	4	4	10 crabapples are from the Rural store in the District data set. Because this are more than the 4 apples in the All Stores data set the New Units number stays at 4.
Rural	Cantalopes	1	1
Rural	Pineapple	2	2

District Data Set

Shop	Type	Amount
Uptown	Bananas	7
Uptown	Strawberries	9
Uptown	Grapes	4
Downtown	Bananas	5
Downtown	Crabapples	3
Downtown	Pears	10
Suburbs	Crabapples	1
Suburbs	Cantalopes	6
Suburbs	Pineapple	2
Rural	Crabapples	10
Rural	Cantalopes	1
Rural	Pineapple	2

[AlB · Accepted solution]

Hi @Anonymous 

Try this for the calculated column 'New Units I'm looking for' 

NewCol =
IF (
    Table1[Item] <> "Apple",
    Table1[Units],
    VAR Crabapples_ =
        LOOKUPVALUE (
            Table2[Amount],
            Table2[Shop], Table1[Store],
            Table2[Type], Table1[Item]
        )
    RETURN
        IF ( Crabapples_ > Table1[Units], Table1[Units], Table1[Units] - Crabapples_ )
)

[Anonymous]

Hi AIB,

Thank you for this solution. It worked on the sample tables I had in my question. But I ran into problems when I tried to apply it to my real data. 

I figured out that I needed more help with this question and made a separate post for it at Handling duplicates when performing a calculation on numbers from two unrelated tables 

Thanks!