Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have 4 different excel dataset connected to power bi ,below are the example of different dataset.
Invoice Date | Paid Fare Inc Tax | Cost Centre1 | Product Area | |
Monday, January 31, 2022 | 11.7 | ABCD | Rail | |
Monday, January 31, 2022 | 11.7 | ABCD | Rail | |
Monday,July 31, 2022 | 11.7 | ABCD | Rail | |
Monday,August 31, 2022 | 11.7 | ABCD | Rail | |
Actual Cost | Cost Centre | Product Area | ||
135 | ABAD | Hotel | ||
135 | ABAD | Hotel | ||
85.5 | ABAD | Hotel | ||
97 | ABAD | Hotel | ||
97 | ABAD | Hotel | ||
Month | GBPGross Cost | Cost Centre 1 | Product Area | |
February | 150 | EEEE | Conference | |
January | 1000 | EEEE | Conference | |
February | 250E | EEEE | Conference | |
February | 250 | EEEE | Conference | |
January | 720 | EEEE | Conference | |
Travel Month | Ticket Number | Paid Fare inc Air Tax | Cost Centre1 | Product Area |
May | 12345 | 119.96 | YYYY | Air |
May | 56789 | 151.96 | YYTY | Air |
May | 22345 | 279.96 | YYAB | Air |
March | 34556 | 70.48 | YYYY | Air |
May | 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 | Previous Month Spend | Current Month Spend |
Air | XXXX | XXXX |
Confrence | XXXX | XXXX |
Rail | XXXX | XXXX |
Hotel | XXXX | XXXX |
Hi @cyborgandy , i think its simple.
1. Create a new table with one column having all unique values of "Product Area".
2. Create one to many relationship from you new table to all the 4 tables.
3. Come to your report page and drag, product area column from your new table and drag all reqiured columns from the other four tables.
Regards,
Nikhil Chenna
Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!
Hi @cyborgandy ,
I wonder whether you can describe more clearly about the calculation logic of "Previous Month Spend" and "Current Month Spend" which you need in your output and the relationships between four tables. Provide clear description in order that we can help you further more.
Best Regards,
Community Support Team _ xiaosun
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
could you also explain the relationships between the four tables?
1. Your second table doesn't have dates. Does the column name 'Actual Cost' mean that you always have values for CURRENT month only in this table? If so, in your resulting table you will only have 'Current Month Spend' for the 'Hotel' Product Area. If not, it will be impossible to determine values for Previous/Current Month Spend.
2. I would append processed tables in Power Query first and then you might create some measures as an option...
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
53 | |
46 | |
16 | |
12 |