Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Performing a calculation on numbers from two unrelated tables

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

StoreItemUnitsNew Units I'm looking forNotes:
UptownApples77No crabapples are from the Uptown store in the District data set
UptownStrawberries99 
UptownGrapes44 
DowntownApples523 crabapples are from the Downtown store in the District data set
DowntownBananas33 
DowntownPears1010 
SuburbsApples101 crabapple is from the Suburb store in the District data set
SuburbsCantalopes66 
SuburbsPineapple22 
RuralApples4410 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.
RuralCantalopes11 
RuralPineapple22 

 

District Data Set

ShopTypeAmount
UptownBananas7
UptownStrawberries9
UptownGrapes4
DowntownBananas5
DowntownCrabapples3
DowntownPears10
SuburbsCrabapples1
SuburbsCantalopes6
SuburbsPineapple2
RuralCrabapples10
RuralCantalopes1
RuralPineapple2

 

1 ACCEPTED SOLUTION
AlB
Super User
Super User

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_ )
)

     

View solution in original post

2 REPLIES 2
AlB
Super User
Super User

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
Not applicable

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!

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.