Creating a new table with a column that is the elementwise sum of columns from two other tables

[Anonymous]

My question seems like a relatively simple one, but I haven't been able to find any answer on this forum that satisfies me.

Table A contains a column of company names and a column of values for the revenue generated by each company ("revenue").

Table B contains a column of company names (although the list is not identical to the list of company names in Table A; in fact, Table B's company names are a subset of Table A's) and a column of costs incurred by each company ("costs").

I would like to create a new table ("Table C") with a list of company names as its first column and (revenues - costs) as the second column. How would I do this?

[parry2k · Accepted solution]

@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.

[Anonymous]

But would that generate a two-column table? I want my result to be something like the following:

Acme Industries, $31909;

Batteries Plus, $1672;

Cranmer and Associates, $8365;

...

Are measures really the way to achieve that?

[parry2k]

@Anonymous yes, measure is the way to achieve it and you will able to get what you are expecting?

[Anonymous]

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.

[parry2k]

@Anonymous you have to add calculated Measure not Column.

[Anonymous]

Sorry for the misreading. I switched them from columns to measures like you said and, holy cow, it actually worked. It's exactly what I wanted. Thanks so much!

[parry2k]

@Anonymous glad to hear. Enjoy!!