Forum Discussion
Pivot many to many relationships
Hello all :)
I'm a total newb at pivot tables and relationships on power pivot. I've watched a couple of videos and tried a few things but I still cannot get to where I want. If anyone can help, it would be greatly appriciated :)
I have 2 tables
One is name of establishments (can be there more than once), amount earned during a certain program and the date of the earning.
The other is those establishments again (often more than once again), the list of expenses they made and the date of the expense.
What I want is a table with the dates as first element of each row, with listed gains and expenses in 2 different rows, with establishment name as a filter on top. So when you pick a est. name, it shows you only their data.
Kinda like this:
I understand that, since one est. can be there many times in both, I can't link them because it creates a many to many. I tried creating a list of all possible establishments stated only onces to link both tables, but it's not working :/
Thank you so much for reading :)
Below are the simple steps, hope this help:
- Create a unique list of establishments
- Set up relationships
- Create a Pivot Table
- 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.
Can you upload your sample file here > https://filetransfer.io/
Unfortunatly no, it has sensitive info :/
Late reply, see the sample file here> https://filetransfer.io/data-package/7YLnIWr5#link
Content: (EST1)
(EST2)
Formula:
=LET( header,{"Date","Gain","Expense"}, dateCol,SORT( UNIQUE( VSTACK( FILTER(Table1[date], Table1[est]=L1, ""), FILTER(Table2[date], Table2[est]=L1, "") ) ) ), gainCol,IFERROR(VLOOKUP(dateCol,Table1,3,FALSE),0), totalGain,SUM(gainCol), expenseCol,IFERROR(VLOOKUP(dateCol,Table2,3,FALSE),0), totalExpense,SUM(expenseCol), gainexpense, (totalGain-totalExpense), totalrow, HSTACK("",totalGain,totalExpense,gainexpense), combinedData,HSTACK(dateCol,gainCol,expenseCol), IFERROR(VSTACK(header,combinedData,totalrow),"") )ouf.. wait i'm not a programmer :/what is this?
Below are the simple steps, hope this help:
- Create a unique list of establishments
- Set up relationships
- Create a Pivot Table
- 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.
Use FILTER() or PIVOTBY() function.
