Forum Discussion
Creating a new table with a column that is the elementwise sum of columns from two other tables
Anonymous the best approach here is to create a company dimension with unique list of companies, set relationship with this company table with you revenue and cost tables
Add following measures
Revenue = SUM(RevenueTable[Revenue]) Cost = SUM( CostTable[Cost] )
Diff = [Revenue] - [Cost]add table visual, and put company from newly create company table, and all above measures.
I successfully made a master list of companies (with no blank entries or duplicates) in its own table.
I then created a one:many relationship from the master list onto Table A's company list, and a one:many relationship from the master list onto Table B's company list.
However, I am having trouble creating the final table along your specifications. I add the master list of company names, which of course works fine. However, when I go to write the formula to create the calculated difference column, it doesn't recognize "revenues" or "costs" as valid entries to perform column operations on. Is there something I'm missing?
Also, the SUM-function doesn't give me what I want. When I use it and then take the difference of the sums, I get the equivalent of:
Acme Industries, $50354;
Banner Corp., $50354;
Cranmer and Associates, $50354;
...
Which is obviously not very useful.