Aug 03 2018 07:01 AM
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.
Aug 03 2018 10:50 PM
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?
Aug 06 2018 07:17 AM
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.
Aug 07 2018 05:23 AM
SolutionHi 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
Jan 21 2019 02:53 AM
Hi Roy,
Did you solve your problem with PivotTable ¿? I have the same problem here.
Thank you.
Humberto
Aug 07 2018 05:23 AM
SolutionHi 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