Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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.
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.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
98 | |
78 | |
65 | |
60 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |