Forum Discussion
Creating a Table that Draws Values from Multiple Sources
Hello Everyone,
I have a problem I have not been able to solve yet - mostly due to how new I am to Power BI and DAX, I think. Basically, I have two data sources that are similarly formated, and I wanted to take particular values within those data sources and place them into the same table in a dashboard.
One data source contains a list of organizations and their budgets across a set number of budget categories. The format looks like this:
| Organization | Personnel | Fringe | Travel | Contractual |
| Seaside Assistance | $50,000 | $15,000 | $700 | $20,000 |
| Coupe Inc. | $25,000 | $7,500 | $500 | $50,000 |
The other data source has the same format, but contains the expenditures for those organizations across the same budget categories. It would be something like this:
| Organization | Personnel | Fringe | Travel | Contractual |
| Seaside Assistance | $2,500 | $700 | $0 | $1,500 |
| Coupe Inc. | $1,000 | $250 | $100 | $10,000 |
What I want to do is create a table that shows the budget amount for each category, the expended amount, and the remaining funds as different columns for an organization that a dashboard user selects via slicer or other tool. For example, if a user wanted to see the budget vs. expended for Seaside Assistance, using the example above, it would look like this once they selected that organization from the slicer:
| Budget Category | Budget | Expended | Remaining |
| Personnel | $50,000 | $2,500 | $47,500 |
| Fringe | $15,000 | $700 | $14,300 |
| Travel | $700 | $0 | $700 |
| Contractual | $20,000 | $1,500 | $18,500 |
Is this possible? Can anyone help me get started with this? I know the last column would be a simple arithmetic measure, but I'm having trouble figuring out how to bring in the budget and expended values into the same table in the format above. Any help would be greatly appreciated!
Thank you!
Hi ZMcNealy,
In Power Query:
Unpivot both Budget and Expenditure tables (columns: Personnel, Fringe, Travel, Contractual).
Rename:
Attribute → Category
Value → Budget or Expended
- Merge the tables on Organization + Category.
- Add a column:
Remaining = [Budget] - [Expended]
In Power BI report:
Add a Slicer for Organization.
Add a Table visual with: Category, Budget, Expended, Remaining.
Let me know if you need further help on this.
⭐Hope this solution helps you make the most of Power BI! If it did, click 'Mark as Solution' to help others find the right answers.
💡Found it helpful? Show some love with kudos 👍 — your support keeps our community thriving!
🚀Let’s keep building smarter, data-driven solutions together! 🚀 [Explore More]
3 Replies
Hi ZMcNealy,
In Power Query:
Unpivot both Budget and Expenditure tables (columns: Personnel, Fringe, Travel, Contractual).
Rename:
Attribute → Category
Value → Budget or Expended
- Merge the tables on Organization + Category.
- Add a column:
Remaining = [Budget] - [Expended]
In Power BI report:
Add a Slicer for Organization.
Add a Table visual with: Category, Budget, Expended, Remaining.
Let me know if you need further help on this.
⭐Hope this solution helps you make the most of Power BI! If it did, click 'Mark as Solution' to help others find the right answers.
💡Found it helpful? Show some love with kudos 👍 — your support keeps our community thriving!
🚀Let’s keep building smarter, data-driven solutions together! 🚀 [Explore More]
Hi ZMcNealy
Thank you for reaching out to the Microsoft Community Forum
GrowthNatives Thank you for your prompt response
ZMcNealy ,Step-by-Step Guide: Merging Budget and Expended Tables in Power BI
Step 1: Import the Data
Import your data tables into Power BI Desktop. You should have at least two tables:Budget
ExpendedStep 2: Unpivot Columns
For both the Budget and Expended tables:Click on the table (Budget or Expended) in Power Query Editor.
Select the following columns: Personnel, Fringe, Travel, Contractual, etc.
Right-click and choose Unpivot Columns.
Rename the resulting columns:
Rename Attribute to Category
Rename Value to Budget or Expended accordingly for each table
Step 3: Merge the Tables
In Power Query Editor, go to Home > Merge Queries.
Merge the Expended table with the Budget table using appropriate join keys (e.g., Organization, Category, or any common identifier).
After merging, expand the Budget table inside the merged query.
Select only the relevant column (e.g., Budget) from the expanded list.
Note: Chage the data types for Budget & ExpendedStep 4: Apply Changes and Load Data
Click Close & Apply to load the transformed data into the data model (Power Pivot).
Step 5: Create Measures
In the data model, create the following DAX measures:SumOfBudget = SUM(MergedTable[Budget])
SumOfExpended = SUM(MergedTable[Expended])
Remaining = [SumOfBudget] - [SumOfExpended]
Step 6: Create Table VisualInsert a Table visual on your report canvas.
Add the following fields to the table:
Category
SumOfBudget
SumOfExpended
RemainingStep 7: Add a Slicer for Filtering
Insert a Slicer visual.
Drag a field like Organization into the slicer to allow filtering by organization.If this solution helped resolve your query, kindly mark it as Accepted and consider giving a Kudos so it can assist others in the community facing similar issues.
Let me know if you need further assistance!
Thanks & Regards,
LakshmiNarayana.
GrowthNativesSorry for the dely in replying, I wanted to implement and test everything before writing back. This worked, thank you so much!
v-lgarikapatThank you for youry reply as well, the screenshots are very helpful. It looks like both solutions are very similar.
Thanks again!
