Power Pivot Table Relationships & Related Rows

[AC1982]

Hi

I have two tables, one that has Site Name, purchase order number and purchase order values and the other is an invoicing table that has Purchase Order Number, Invoice Number, Invoice Amount.

I created a relationship between the Purchase Order Number Fields in both tables.

I did this so that I could see how much has been invoiced against each site

When I use the data model to create my pivot table, I thought that based on the relationship I created that the table would like -

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Site Name
[/TD]
[TD]Purchase Order #
[/TD]
[TD]Purchase Order Value
[/TD]
[TD]Invoice Number
[/TD]
[TD]Invoice Amount
[/TD]
[/TR]
[TR]
[TD]NYC
[/TD]
[TD]123678
[/TD]
[TD]$100
[/TD]
[TD]INV04
[/TD]
[TD]$30
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]INV03
[/TD]
[TD]$70
[/TD]
[/TR]
[TR]
[TD]LA
[/TD]
[TD]123453
[/TD]
[TD]$200
[/TD]
[TD]INV01
[/TD]
[TD]$30
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]INV02
[/TD]
[TD]$100
[/TD]
[/TR]
</tbody>[/TABLE]


Instead what has happened is that when I drag the Invoice Number into the Rows section of the pivot table all the Invoice Numbers appear under each Site Name and PO value, regardless of whether or not the invoice number shows are linked to the purchase order values, like in the table below -

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Site Name
[/TD]
[TD]Purchase Order #
[/TD]
[TD]Purchase Order Value
[/TD]
[TD]Invoice Number
[/TD]
[TD]Invoice Amount
[/TD]
[/TR]
[TR]
[TD]NYC
[/TD]
[TD]123678
[/TD]
[TD]$100
[/TD]
[TD]INV01
[/TD]
[TD]$30
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]INV02
[/TD]
[TD]$70
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]INV03
[/TD]
[TD]$30
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]INV04
[/TD]
[TD]$100
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]INV05
[/TD]
[TD]$500
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Does anyone know what I have done wrong? I only want the invoice numbers that correspond to the related purchase order to be shown against each PO.

The purchase order is unique but some sites might have multiple purchase orders against them.

Thanks

 

[scottsen]

I'm guessing you have nothing in the values area of your pivot table? Trying adding a measure like [Total Invoice] := SUM(Invoices[Amount]) ?