Creating a Table that Draws Values from Multiple Sources

[ZMcNealy]

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!

[ZMcNealy]

@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!

[v-lgarikapat]

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
Expended

Step 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 & Expended

Step 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 Visual

Insert a Table visual on your report canvas.

Add the following fields to the table:

Category
SumOfBudget
SumOfExpended
Remaining

Step 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.

[GrowthNatives · Accepted solution]

Hi @ZMcNealy,

1. In Power Query:

   • Unpivot both Budget and Expenditure tables (columns: Personnel, Fringe, Travel, Contractual).

   • Rename:

     • Attribute → Category

     • Value → Budget or Expended

2. Merge the tables on Organization + Category.
3. Add a column:

   • Remaining = [Budget] - [Expended]

4. 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]