cancel
Showing results for
Did you mean:
Helper II

## New Column with value of another table based

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!

2 ACCEPTED SOLUTIONS
Super User I

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

Proud to be a Super User!

Super User I

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

Proud to be a Super User!

5 REPLIES 5
Super User I

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

Proud to be a Super User!

Super User I

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

Proud to be a Super User!

Helper II

 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%
Super User I

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

Proud to be a Super User!

Helper II

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.

Announcements