cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lotus22
Helper II
Helper II

New Column with value of another table based

Table 1

RegionType of costCostYear
APInfrastructure15002019
APApplication5002019
APInfrastructure15002020
APApplication6002020
NAInfrastructure21002020
NAApplication18002019
NAInfrastructure24002020
NAApplication22002019

 

Table 2

YearNA SalesAP SalesIT AP CostIT NA Cost
201960000020000020004000
202065000022000021004500

 

 

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

Hi @lotus22 ,

Created the tables then created a relationship between the two, then created two measures.Combine 2.PNGCombine.PNG

 

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")

 

Combine 23.PNG

 

 

 

 

 

 

 

 

 

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!




View solution in original post

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]))

 

Combine 234.PNG


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!




View solution in original post

5 REPLIES 5
Nathaniel_C
Super User I
Super User I

Hi @lotus22 ,

Created the tables then created a relationship between the two, then created two measures.Combine 2.PNGCombine.PNG

 

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")

 

Combine 23.PNG

 

 

 

 

 

 

 

 

 

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!




View solution in original post

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]))

 

Combine 234.PNG


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!




View solution in original post

lotus22
Helper II
Helper II

Hi Nathan, Please see below

 

Year20192020
NA Sales600000650000
AP Sales200000220000
IT AP Cost20002100
IT NA Cost40004500
IT AP %1.00%0.95%
IT NA %0.67%0.69%
IT Global0.75%0.76%
Nathaniel_C
Super User I
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!




lotus22
Helper II
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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

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.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.