Combining two data table

[sathish_Japan]

Hi Everyone,
I have two tables one with revenue & cost.
I want to combine both.my situation is as below,
Data table

Table I
nature Product Customer Revenue
Revenue A ABC 100
Revenue B DEF 200
Revenue C GHI 300

Table II
nature Product Customer cost
Cost A ABC 60
Cost B DEF 120
Cost C GHI 180


Power Pivot mapping - I have mapped the products


Current Result - I get two different column one for revenue & one for cost


Row Labels Sum of Revenue Sum of Cost
Revenue 2100 1260
Cost 2100 1260


Expected result : I just need one help that gives both revenue & cost.

Row Labels Amt
Revenue 2100
Cost 1260

Can you please help?

Thank you so much for taking time to look into this

 

[Matt Allington]

Create a third table called product that contains a complete of all the possible products. Remove the current relationship and instead join both data tables to the new product lookup table. Use the lookup table on the pivot table rows.

Read about data structure in my knowledge base here
The Optimal Shape for Power Pivot Data -

 

[sathish_Japan]

Create a third table called product that contains a complete of all the possible products. Remove the current relationship and instead join both data tables to the new product lookup table. Use the lookup table on the pivot table rows.

Read about data structure in my knowledge base here
The Optimal Shape for Power Pivot Data -

If I create third table, I still need to add revenue & cost seperatly so there would data against a product in two column & not single column

 

[Matt Allington]

Are you asking a question? If so, I don't understand what it is. Just create the third table and join it as I described. Put the column from the third table on the rows of your pivot table, then add the 2 columns to be summed just like you described in your original post.