Calculate measure two different tables

[lekkerbek]

Hi,

Can you somebody please help me with a measure?

I have 2 tables:

- Customers

- Transactions

The customer table has the following relevant columns:

- accountcode

- accountname

- candropship (true or false)

The transaction table has the following relevant columns:

- date

- accountcode

- amountdc

I would like to create a measure that calculates the total sum of all dropshipment customers.

I've created the following, but that doesn't work: 

CALCULATE(SUM(Transactions[AmountDC]);DimRelaties[CanDropShip]="True")

Thank you in advance.

[az38]

Hi @lekkerbek 

as i see your data model to calculate dropshipment customers you need something like that

calculate(countrows('Customers');ALL('Customers');'Customers'[candropship]=true())

if you need a count of transactions which made by dropshipment customers you need something like that

calculate(countrows('Transactions');ALL('Customers');'Customers'[candropship]=true())

and dont forget to createrelationships beween these tables

what is the table DimRelaties?

do not hesitate to give a kudo to useful posts and mark solutions as solution

[lekkerbek]

Hi @az38,

Thank you for your suggestion. What I need is the total revenue of the dropshipment customers.

I tried to rewrite your code to, but I think this gives me the number of transactions rather than the revenue: calculate(sum(Transacties[AmountDC]);ALL('DimRelaties');'DimRelaties'[CanDropShip]=true())

Basically "transacties = transactions table" and "DimRelaties = customers table"

I forgot to translate that into English.

[v-lid-msft]

Hi @lekkerbek ,

Based on my test, your formula works fine with our sample data, could you please figure the difference in data model between ours and yours?

Best regards,

[lekkerbek]

Thank you both. I will look into this in more detail as there seems to be something wrong with my model. The loopupvalue function also gives no results.

At least now I know the formula is correct. Thanks.

[az38 · Accepted solution]

@lekkerbek 

your statement

calculate(sum(Transacties[AmountDC]);ALL('DimRelaties');'DimRelaties'[CanDropShip]=true())

looks pretty good to calculate total revenue by ALL customers which candropship is true

isn't it work ok? give a data example please

do not hesitate to give a kudo to useful posts and mark solutions as solution