Pivot many to many relationships

Below are the simple steps, hope this help:

1. Create a unique list of establishments
2. Set up relationships
   •  
3. Create a Pivot Table
4. Adjust the Pivot Table

Step by Step:

• Unique Establishments Table:
  • In Excel, create a new sheet and paste all establishment names from both tables.
  • Use the "Remove Duplicates" feature to get a unique list.
• Import Tables into Power Pivot:
  • Go to the Power Pivot tab and click on "Manage".
  • Import your earnings, expenses, and unique establishments tables.
• Create Relationships:
  • In the Power Pivot window, go to the "Design" tab and click on "Create Relationship".
  • Create a relationship between the unique establishments table and the earnings table using the establishment name.
  • Create another relationship between the unique establishments table and the expenses table using the establishment name.
• Build the Pivot Table:
  • Insert a Pivot Table from the Power Pivot window.
  • Drag the date field to the rows area.
  • Drag the amount earned field to the values area.
  • Drag the expenses field to the values area.
  • Drag the establishment name from the unique establishments table to the filters area.