how to calculate between two columns from diffrent tables?

[davidi4524]

hi, i have two tables , one is a branch table wichh includ the branch name and profit target.

and another table wich inculde branche and total profit.

i whant to create a colum tahat make a "branch profit targer - total profit.

for example: cranch number one - the profit target is 1500

and the total profit is 2100

i whant to to 2100 - 1500 = 600

so the colum will show "600".

thanks for respondes.

david.

[Sean]

Create a Column in your 'Another Table' (I've assumed the 2 tables are related by Branch)

Over/Under = 'Another Table'[Total Profit] - RELATED('Branch Table'[Profit Target])

Hope this helps!

[davidi4524]

hi, i get this error:

A single value for column 'totalprof' in table 'totalprofit' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

what to do?

[Sean]

The above is a Column formula you are creating a Measure that's why you are getting that error.

select the Table you want the column to be created in - click New Column and then enter the formula

[davidi4524]

its not working for me 😞

now this error:

The column 'target[target]' either doesn't exist or doesn't have a relationship to any table available in the current context.

please help me

[Sean]

Are the 2 tables related?

[davidi4524]

they have a relationship thru another table but they are no related. should i try to related them?

[davidi4524]

its imposible to related them because non of this table have a uniqe value. they have a relationship only thru branches table.

its must be a direct relationship?

[Sean]

Okay then forget about the relationship

Create this Profit Target COLUMN in your 'Another Table'

Profit Target COLUMN = 
CALCULATE (
    LOOKUPVALUE (
        'Branch Table'[Profit Target],
        'Branch Table'[Branch Name], VALUES ( 'Another Table'[Branch] )
    )
)

Then you just subtract the 2 columns

Over/Under = 'Another Table'[Total Profit] - 'Another Table'[Profit Target COLUMN] 

Hope this helps!

[davidi4524]

didnt work for me. attach a picture

[Sean · Accepted solution]

Medeling tab => New Table

Summary Table =
SUMMARIZE (
    'TotalProfit',
    'TotalProfit'[Branch Name],
    'TotalProfit'[Date],
    "Profit", CALCULATE ( SUM ( TotalProfit[Profit] ) ),
    "Target", LOOKUPVALUE (
        'Targets'[Target],
        'Targets'[Branch Name], 'TotalProfit'[Branch Name],
        'Targets'[Date], 'TotalProfit'[Date]
    )
)

 then in this new Summary Table create a column

Column = 'Summary Table'[Profit] - 'Summary Table'[Target]

[davidi4524]

tanks you !! its work for me 🙂 thanks for this great solution.

[davidi4524]

hi, when i tried to calculate this in a tables with more than one ling' i got this error: "A table of multiple values was supplied where a single value was expected."

there is a snapshot

please help me with this. thanks.

[davidi4524]

hi, when i tried to calculate this in a tables with more than one ling' i got this error: "A table of multiple values was supplied where a single value was expected."

there is a snapshot

please help me with this. thanks.

[Phil_Seamark]

How about making a combined table.  You can do this in the Query Editor as well as in DAX.

If you post more detail about your table structure, we can probably provide clearer step by step instructions.

[davidi4524]

ok its go like this:

table 1 - named "brances" - contain the brances names

table 2 named "total profit" and contain the branch name , the month, and the total profit for month

table 3 named "targets" contain the branch name , the month ' and the target.

there is a relationship between "brances" and "total profit" with the colum "branch name"

and there is a relationship between "targets" and "brances" with the colum "branch name"

so if i choose a brach from the "branche" table' i got the numbers of the target and the total profit ok.

not i just whant to creatr a colum that will make the callculate between total profit - target.

thank you vry much for your help.

[Phil_Seamark]

Hi @davidi4524,

Do you have relationships setup between the two tables?  So this is a direct relationship, not one that passes through another table.