SOLVED

PivotTable Error with Relationships

Copper Contributor

Hello,

Could someone help me with a pivot table issue? I am having difficulties combining 3 tables together. I created a one to many relationship from table 1 to table 2 and table 3 as shown by the arrows in the below image. However, when I add a pivot table, all of the invoice numbers are showing up under every purchase order number. How do I set up the pivot table so that only the corresponding invoice number shows up under the purchase order, and blank if there is no corresponding invoice.

I tried the auto-detect in case I was missing something and 0 results came up. 

 

Excel Pivot Table Issue.JPG

4 Replies

Hi Roy

 

If you remove Sum of PO amount and Sum of Ledger amount do you get the answer you want?

 

Also, are you using Power Pivot or just the relationship buttons on the data ribbon?

I do not quite get the answer I am looking for when I remove Sum of PO amount and Sum of Ledger amount. Is there any way to have the pivot table show something like this screen shot?

 

Excel PO Pivot Table example.JPG

 

I am using just the relationship buttons on the data ribbon. 

best response confirmed by Roy Roongseang (Copper Contributor)
Solution

Hi Roy

 

I can't think of a straightforward way to do that.  

 

Potentially could be done with some DAX formula using Power Pivot but if you've not used that before this isn't the type of thing that can be explained here sorry.

 

I'd go for a couple of separate pivot tables then add a slicer connecting to both as per the attached maybe

Hi Roy,

Did you solve your problem with PivotTable ¿? I have the same problem here.

https://techcommunity.microsoft.com/t5/Excel/PivotTable-Error-with-Relationships-using-Data-Model/m-...

 

Thank you.

Humberto

1 best response

Accepted Solutions
best response confirmed by Roy Roongseang (Copper Contributor)
Solution

Hi Roy

 

I can't think of a straightforward way to do that.  

 

Potentially could be done with some DAX formula using Power Pivot but if you've not used that before this isn't the type of thing that can be explained here sorry.

 

I'd go for a couple of separate pivot tables then add a slicer connecting to both as per the attached maybe

View solution in original post