cancel
Showing results for
Search instead 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

Did I answer your question? Mark my post as a solution!

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

Did I answer your question? Mark my post as a solution!

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

Did I answer your question? Mark my post as a solution!

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

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!  Helper II

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%  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

Did I answer your question? Mark my post as a solution!

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.  ## Helpful resources

Announcements #### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group #### The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings. #### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event. Top Solution Authors
Top Kudoed Authors
Users online (13,863)