New Column with value of another table based

[lotus22]

Table 1

Region	Type of cost	Cost	Year
AP	Infrastructure	1500	2019
AP	Application	500	2019
AP	Infrastructure	1500	2020
AP	Application	600	2020
NA	Infrastructure	2100	2020
NA	Application	1800	2019
NA	Infrastructure	2400	2020
NA	Application	2200	2019

Table 2

Year	NA Sales	AP Sales	IT AP Cost	IT NA Cost
2019	600000	200000	2000	4000
2020	650000	220000	2100	4500

I would like to bring "total" cost information in the Sales table (Table2) from Table 1. Is there an easy way to do this?

Thank you!

[Nathaniel_C · Accepted solution]

Hi @lotus22 ,

Created the tables then created a relationship between the two, then created two measures.

Sum of IT AP Cost = CALCULATE(SUM('Table 1'[Cost]),'Table 1'[Region]= "AP")
Sum of IT NA Cost = CALCULATE(SUM('Table 1'[Cost]),'Table 1'[Region]= "NA")

Will get back on rest,

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Nathaniel_C · Accepted solution]

Hi @lotus22 ,

Created the tables then created a relationship between the two, then created two measures.

Sum of IT AP Cost = CALCULATE(SUM('Table 1'[Cost]),'Table 1'[Region]= "AP")
Sum of IT NA Cost = CALCULATE(SUM('Table 1'[Cost]),'Table 1'[Region]= "NA")

Will get back on rest,

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Nathaniel_C · Accepted solution]

Hi @lotus22 ,

% of AP = DIVIDE([Sum of IT AP Cost],SUM('Table (2)'[AP Sales]))
% of NA = DIVIDE([Sum of IT NA Cost],SUM('Table (2)'[NA Sales]) )
% of Global Sales = DIVIDE([Sum of IT AP Cost]+[Sum of IT NA Cost],SUM('Table (2)'[AP Sales])+Sum('Table (2)'[NA Sales]))

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[lotus22]

Hi Nathan, Please see below

Year	2019	2020
NA Sales	600000	650000
AP Sales	200000	220000
IT AP Cost	2000	2100
IT NA Cost	4000	4500
IT AP %	1.00%	0.95%
IT NA %	0.67%	0.69%
IT Global	0.75%	0.76%

[Nathaniel_C]

Hi @lotus22 ,
Would you please provide a table that shows expected results.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[lotus22]

By the way, I am trying to calculate the percentage of IT cost to Total sales. I wasn't sure how else to capture this.