cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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
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

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




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
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

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.

Top Solution Authors
Top Kudoed Authors