Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
Proud to be a Super User!
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!
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!
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!
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% |
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!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |