Forum Discussion
Pivot - Several databases - Many to Many
Good Afternoon,
I'm new in PowerBI, thank you in advance for your help. I'm trying to connect 2 databases in one PIVOT but the result is not correct. I would like to show in a pivot the number of complaints and the sales.
Database 1 is :
| Month | Week | Customer | Product | Number of Complaint | |
| 2018 | 5 | 12 | A | Chips1 | 1 | |
| 2018 | 4 | 13 | B | Chips 2 | 1 | |
| 2018 | 5 | 12 | A | Chips1 | 1 |
Database 2
| Month | Week | Customer | Product | Sales | |
| 2018 | 5 | 12 | A | Chips1 | 100000 | |
| 2018 | 4 | 13 | B | Chips 2 | 500000 | |
| 2018 | 5 | 12 | A | Chips 1 | 85000 |
DESIRED OUTPUT
| Month | Week | Customer | Product | Sum Sales | Sum complaint | |
| 2018 | 5 | 12 | A | Chips1 | 185000 | 2 | |
| 2018 | 4 | 13 | B | Chips 2 | 6000000 | 55 |
What I Did :
- I connected both databases via Many to Many for customer
- Create a Pivot
- Select "customer" of the complaint for the row of the Pivot
- Select sales and complaint for the values
What I have :
| Month | Week | Customer | Product | Sum Sales | Sum complaint | |
| 2018 | 5 | 12 | A | Chips1 | 6185000 | 2 | |
| 2018 | 4 | 13 | B | Chips 2 | 6185000 | 55 |
Sum of sales is incorrect because the connection between databases doesn't work properly. Could you please help me ?
Many thanks,
Hi,
In order to make your datamodel work properly, you ideally create a Customer Dimension.
This is a list with all unique Customers and optional more information on them.When you created this, you can simply connect both as you call it "databases" to the same table with a 1 to many connection(always try to stay from many to many)
How to create this?
Idealy, just go to PowerQuery --> Right click on a table that contains all Customers --> Reference --> Remove all columns except customer(key) --> Remove duplicate values of Customers
Now you have a list of all unique customers, this you can connect to all other tables containing multiple customer rows.
5 Replies
Hi Anonymous ,
I suggest to concatenate Date, Customer and Product columns in both databases. Then, link both databases via the concatenated columns.
That should do it.
Let me know if anything is unclear.
Cheers,
Robin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hello Anonymous ,
Thank you for your answer. I didn want to concatenate theses columns because I need to use them separately. Finaly I renamed the columns with exactly the same name then it works but I have the same problem with another column.
Indeed, if I want to see the complaints and sales by week, I have the same problem :
DESIRED OUTPUT
Week Complaints Sales 12 18 789456 13 5 546585 14 25 45821 15 23 85468 16 85 87458 17 220 88754 What I have :
Week Complaints Sales 12 376 789456 13 376 546585 14 376 45821 15 376 85468 16 376 87458 17 376 88754 When I do "see records", I see that only one of the database is properly filtered as you can see below :
Many thanks for your help :)
Hi,
In order to make your datamodel work properly, you ideally create a Customer Dimension.
This is a list with all unique Customers and optional more information on them.When you created this, you can simply connect both as you call it "databases" to the same table with a 1 to many connection(always try to stay from many to many)
How to create this?
Idealy, just go to PowerQuery --> Right click on a table that contains all Customers --> Reference --> Remove all columns except customer(key) --> Remove duplicate values of Customers
Now you have a list of all unique customers, this you can connect to all other tables containing multiple customer rows.