Forum Discussion

Louis63's avatar
Louis63
New Member
3 years ago
Solved

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 :

 

Thanks for your help.

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.

    • Louis63's avatar
      Louis63
      New Member
      3 years ago

      Hello Anonymous , 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

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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.