SOLVED
Home

PivotTable Error with Relationships

%3CLINGO-SUB%20id%3D%22lingo-sub-223638%22%20slang%3D%22en-US%22%3EPivotTable%20Error%20with%20Relationships%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-223638%22%20slang%3D%22en-US%22%3E%3CP%3E%3CIMG%20src%3D%22http%3A%2F%2Ftinypic.com%2Fr%2Fdcfu4j%2F9%22%20border%3D%220%22%20alt%3D%22%22%20%2F%3E%3CSPAN%3EHello%2C%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3ECould%20someone%20help%20me%20with%20a%20pivot%20table%20issue%3F%20I%20am%20having%20difficulties%20combining%203%20tables%20together.%20I%20created%20a%20one%20to%20many%20relationship%20from%20table%201%20to%20table%202%20and%20table%203%20as%20shown%20by%20the%20arrows%20in%20the%20below%20image.%20However%2C%20when%20I%20add%20a%20pivot%20table%2C%20all%20of%20the%20invoice%20numbers%20are%20showing%20up%20under%20every%20purchase%20order%20number.%26nbsp%3BHow%20do%20I%20set%20up%20the%20pivot%20table%20so%20that%20only%20the%20corresponding%20invoice%20number%20shows%20up%20under%20the%20purchase%20order%2C%20and%20blank%20if%20there%20is%20no%20corresponding%20invoice.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI%20tried%20the%20auto-detect%20in%20case%20I%20was%20missing%20something%20and%200%20results%20came%20up.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F38860i02285768247CCEFD%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Excel%20Pivot%20Table%20Issue.JPG%22%20title%3D%22Excel%20Pivot%20Table%20Issue.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-223638%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPivotTable%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-321534%22%20slang%3D%22en-US%22%3ERe%3A%20PivotTable%20Error%20with%20Relationships%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-321534%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Roy%2C%3C%2FP%3E%3CP%3EDid%20you%20solve%20your%20problem%20with%20PivotTable%20%C2%BF%3F%20I%20have%20the%20same%20problem%20here.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2FPivotTable-Error-with-Relationships-using-Data-Model%2Fm-p%2F321453%2Fhighlight%2Ftrue%23M24369%22%20target%3D%22_self%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2FPivotTable-Error-with-Relationships-using-Data-Model%2Fm-p%2F321453%2Fhighlight%2Ftrue%23M24369%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3CP%3EHumberto%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-225148%22%20slang%3D%22en-US%22%3ERe%3A%20PivotTable%20Error%20with%20Relationships%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-225148%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Roy%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20can't%20think%20of%20a%20straightforward%20way%20to%20do%20that.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPotentially%20could%20be%20done%20with%20some%20DAX%20formula%20using%20Power%20Pivot%20but%20if%20you've%20not%20used%20that%20before%20this%20isn't%20the%20type%20of%20thing%20that%20can%20be%20explained%20here%20sorry.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'd%20go%20for%20a%20couple%20of%20separate%20pivot%20tables%20then%20add%20a%20slicer%20connecting%20to%20both%20as%20per%20the%20attached%20maybe%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-224669%22%20slang%3D%22en-US%22%3ERe%3A%20PivotTable%20Error%20with%20Relationships%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-224669%22%20slang%3D%22en-US%22%3E%3CP%3EI%20do%20not%20quite%20get%20the%20answer%20I%20am%20looking%20for%20when%20I%20remove%20Sum%20of%20PO%20amount%20and%20Sum%20of%20Ledger%20amount.%20Is%20there%20any%20way%20to%20have%20the%20pivot%20table%20show%20something%20like%20this%20screen%20shot%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F39032iA4A5CA80E1642549%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22Excel%20PO%20Pivot%20Table%20example.JPG%22%20title%3D%22Excel%20PO%20Pivot%20Table%20example.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20using%20just%20the%20relationship%20buttons%20on%20the%20data%20ribbon.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-224262%22%20slang%3D%22en-US%22%3ERe%3A%20PivotTable%20Error%20with%20Relationships%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-224262%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Roy%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20remove%20Sum%20of%20PO%20amount%20and%20Sum%20of%20Ledger%20amount%20do%20you%20get%20the%20answer%20you%20want%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAlso%2C%20are%20you%20using%20Power%20Pivot%20or%20just%20the%20relationship%20buttons%20on%20the%20data%20ribbon%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Roy Roongseang
New 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. 

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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies