Forum Discussion

Helper IV

6 years ago

Solved

Calculate measure two different tables

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
6 years ago
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

5 Replies

az38
Community Champion
6 years ago
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
  Helper IV
  6 years ago
  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.
  - az38
    Community Champion
    6 years ago
    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