MS Excel 2013 PowerPivot - Making only 1 [Amount] column which takes from multiple tables [Amount]

[dluhut]

Hello,

I have multiple tables that I have I add to data model, in which I create a pivot table.

However, since the [Amount] column were from different tables, pivot table is showing me 3 different columns, instead of 1.

May I know, how do I go about doing it and show it like the 'classical' pivot table.

Below is an image of how the tables were done, and the desired result.

 

[FranzV]

Try the following:

• Relate the 3 fProduct tables to dProduct using Product as key.
• Create one measure to sum each table.
  • [Amount X]:= SUM( fProductX[Amount] )
  • Where X could be A, B, or C.
• Create a unifying measure that adds the three other measures.
  • [Total Amount]:= [Amount A] + [Amount B] + [Amount C]

Placing dProduct[Product] as rows in your pivot should propagate the filter to the other tables.

 

[dluhut]

Genius!!!

Thanks FranzV!!!

Learn something new again from you.

Like and hopefully your reputation goes up!