Forum Discussion
Will the custom column values will change with updated values from data source
Hi ST_OK
If you'd like to calculate the impact for every year per client, please creat a calculated column as below:
It works when there is more data updated.
Based on my knowledge, your formula also works after more data updated.
My formula can group data based on "year" and "client".
Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks amitchandak for sharing the insightful post.
To give you more calirifcation, I have 3 tables (spend data by year) and I did an append query to combine them together and created custom column i.e. Impact by year (created by respective spend for that yr). My aim is to compare the spend with impact/spend data I already have and using that to calculate new Impact by year values.
e.g Impact for 2020=(Spend 2020 * Impact)/Spend
* I already have impact and spend data i.e. I know how impact (1000) is generated by spend (50K)
Based on your answer, I will assume the Impact for 2020 will update when I update the source table (Retriving data from salesfroce)
I tried to explain my scenario as much as I can. Waiting for t=your comment on this.
Community SupportHi ST_OK
Do you have tables like:
| year | spend | impact |
| 2018 | 1 | 2 |
| 2019 | 2 | 3 |
| 2020 | 3 | 4 |
e.g Impact for 2020=(Spend 2020 * Impact)/Spend
"Spend" is not the same with "Spend 2020", "Impact" is not the same as "Impact for 2020", right?
If so, how to define them based on my table above?
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Yes, very close. Below will be my final table where Impact is a custom column i created and using append query I have combined 3 tables of 2018^, 2019 and 2020 values.
Client spend 2018 impact 2018 spend 2019 Impact 2019 ABC 45K 90 30K 60 Metro 90K 405 55K 247 ^Table For 2018
Client Spend Year Impact for 100K Spend 2018 Impact (Custom column) ABC 45K 2018 200 =( Spend * Impact for 100K ) / 100K Metro 90K 2018 450 =( Spend * Impact for 100K ) / 100K "Spend" is not the same with "Spend 2020", "Impact" is not the same as "Impact for 2020", right? Yes
Q. Will my custome column values will automatically update when I update my data source? i.e when I add more spend and client info in table for 2018, 2019, 2020?
I have tried to explain as much I can. Appricitae for your help. 🙂- v-juanli-msft
Hi ST_OK
If you'd like to calculate the impact for every year per client, please creat a calculated column as below:
It works when there is more data updated.
Based on my knowledge, your formula also works after more data updated.
My formula can group data based on "year" and "client".
Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Thanks Maggie!
Appriciate the detailed explaination. Cheers!