Forum Discussion
PivotTable Error with Relationships
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.
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
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?
I am using just the relationship buttons on the data ribbon.
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
