Forum Discussion
Need Help with Creating Table from Four Different Datasets
I have 4 different excel dataset connected to power bi ,below are the example of different dataset. All are in different excel sheets and connected through sharepoint folder
| Month | Rail Spend | Cost Centre1 | Product Area | |
| Sep | 11.7 | ABCD | Rail | |
| Sep | 11.7 | ABCD | Rail | |
| Oct | 11.7 | ABCD | Rail | |
| Oct | 11.7 | ABCD | Rail | |
| Month | Hotel Spend | Cost Centre | Product Area | |
| Sep | 135 | ABAD | Hotel | |
| Sep | 135 | ABAD | Hotel | |
| Oct | 85.5 | ABAD | Hotel | |
| Oct | 97 | ABAD | Hotel | |
| Month | Confrence Spend | Cost Centre 1 | Product Area | |
| Sep | 150 | EEEE | Conference | |
| Sep | 1000 | EEEE | Conference | |
| Oct | 250E | EEEE | Conference | |
| Oct | 250 | EEEE | Conference | |
| Oct | 720 | EEEE | Conference | |
| Travel Month | Ticket Number | Air Spend | Cost Centre1 | Product Area |
| Sep | 12345 | 119.96 | YYYY | Air |
| Sep | 56789 | 151.96 | YYTY | Air |
| Oct | 22345 | 279.96 | YYAB | Air |
| Oct | 34556 | 70.48 | YYYY | Air |
| Oct | 22334 | 161.98 | YYCA | Air |
I need to build below table in power bi,could someone help if it is possible as the value is in different datasets
| Product Area | Sep Spend | Oct Spend |
| Air | XXXX | XXXX |
| Confrence | XXXX | XXXX |
| Rail | XXXX | XXXX |
| Hotel | XXXX | XXXX |
cyborgandy
Please refer to attched sample file and the following screenshots
4 Replies
Hi cyborgandy
what is the data type of the month columns?
you can have a date table or simply a month table that filters the four tables 1-*
the rest is just 4 simple measures (one for each metric table) like SUM ( 'Table'[Spend Column] )
use a matrix visual, place the month column from the dimension table at the columns of the matrix and activate the 'Values on rows" option
You need to create one to many relationships between the dimension table and the four tables.
