locked
Power Pivot combining two data table RRS feed

  • Question

  • 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

    Saturday, February 6, 2016 9:22 AM

Answers

  • Another approach, if you're not able to combine the source tables:

    Create a measure Amount:=SUM(TableI[Revenue] + SUM(TableII[Cost])

    Create a small table called 'AmountType' or something, with just one column and two rows: "Revenue" and "Cost". You will use this column for the row labels in your pivot table. Create a relationship from TableI[nature] to AmountType, and from TableII[nature] to AmountType.

    For this to work properly, you should have tables for Product and Customer as well and relate both tables to those.

    • Proposed as answer by Charlie Liao Monday, February 22, 2016 8:57 AM
    • Marked as answer by Charlie Liao Tuesday, February 23, 2016 8:13 AM
    Monday, February 8, 2016 7:38 AM
    Answerer

All replies

  • If you want to report these together I would suggest that you just load them into the one table. Rename the Revenue and Cost columns to Amount, union the two source queries together and report them as one table.

    http://darren.gosbell.com - please mark correct answers

    Monday, February 8, 2016 2:44 AM
  • Excel 2010 with free PowerPivot and Power Query Add-Ins.
    Compatible with Office 2013/2016 Pro Plus.
    Method per Darren.
    Share file if examples don't agree exactly.
    http://www.mediafire.com/download/wg5lz7l9g1log7l/02_07_16.xlsx

    Monday, February 8, 2016 4:12 AM
  • Another approach, if you're not able to combine the source tables:

    Create a measure Amount:=SUM(TableI[Revenue] + SUM(TableII[Cost])

    Create a small table called 'AmountType' or something, with just one column and two rows: "Revenue" and "Cost". You will use this column for the row labels in your pivot table. Create a relationship from TableI[nature] to AmountType, and from TableII[nature] to AmountType.

    For this to work properly, you should have tables for Product and Customer as well and relate both tables to those.

    • Proposed as answer by Charlie Liao Monday, February 22, 2016 8:57 AM
    • Marked as answer by Charlie Liao Tuesday, February 23, 2016 8:13 AM
    Monday, February 8, 2016 7:38 AM
    Answerer