cancel
Showing results for
Did you mean:
New Member

## Calculated column with differente table

Hello, I am looking to create a calculated column that gives the turnover generated per customer.
Knowing that each sale made is in another table, how can I retrieve the amount of sales for a customer knowing that it is possible that this customer has made several orders?

I would point out that the "Customer Key" column appears in both tables :

1 ACCEPTED SOLUTION
Community Support

Hi @Louis63 ,

You can try SUM() function.

``````Turnover Generated =
CALCULATE (
SUM ( 'FACT_Sales 2019-2021'[Turnover] ),
FILTER (
'FACT_Sales 2019-2021',
'FACT_Sales 2019-2021'[Customer Key] = EARLIER ( 'TableName'[Customer Key] )
)
)``````

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 REPLIES 3
Community Support

Hi @Louis63 ,

Please try this code to create a calculated column.

``````Turnover Generated =
CALCULATE (
DISTINCTCOUNT ( 'FACT_Sales 2019-2021'[Turnover] ),
FILTER (
'FACT_Sales 2019-2021',
'FACT_Sales 2019-2021'[Customer Key] = EARLIER ( 'TableName'[Customer Key] )
)
)``````

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

New Member

Hello @v-rzhou-msft , thanks for helping.

You almost get it. The calculated column you propose gives the number of times the customer appears in the "Sales 2019-2021" table. Do you know how to calculate the amount of turnover generated in the "Turnover" column instead?

Sales 2019-2021 table:

Customers table:

Thanks a lot

Community Support

Hi @Louis63 ,

You can try SUM() function.

``````Turnover Generated =
CALCULATE (
SUM ( 'FACT_Sales 2019-2021'[Turnover] ),
FILTER (
'FACT_Sales 2019-2021',
'FACT_Sales 2019-2021'[Customer Key] = EARLIER ( 'TableName'[Customer Key] )
)
)``````

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors