Forum Discussion

Copper Contributor

Aug 03, 2018

Solved

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 ...

Wyn Hopkins
Aug 07, 2018
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
Pivot Table Example (1).xlsx255 KB

MVP

Aug 04, 2018

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?

Roy Roongseang
Copper Contributor
Aug 06, 2018
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.
Pivot Table Example.xlsx239 KB
- Wyn Hopkins
  MVP
  Aug 07, 2018
  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
  Pivot Table Example (1).xlsx255 KB
  - Humberto_Herrera
    Copper Contributor
    Jan 21, 2019
    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-p/321453/highlight/true#M24369
    
    Thank you.
    Humberto

Resources