One-to-Many Relationship; Matrix Layout

[Anonymous]

Hello,

I have a report I am trying to consolidate. This report involves gathering data on a transactional basis, summing it, and comparing it to a budget.

For example, I have 3 employees who have made the following purchases shown below in a table:

Name	Transaction Amount
John Appleseed	$50
John Appleseed	$125
John Appleseed	$60
John Appleseed	$70
David Smith	$558
David Smith	$143
David Smith	$369
Bill Gates	$258
Bill Gates	$749
Bill Gates	$319
Bill Gates	$178
Bill Gates	$2,589
Bill Gates	$14,563
Bill Gates	$1,485,857

I also have the budgets for these employees in another table:

Name	Budget
John Appleseed	$1,000
David Smith	$2,000
Bill Gates	$2,000,000

What I am wanting to do is have Power BI summarize the transactions and then display the budget next each employee with a budget remaining column. All of this done in a matrix and viewed like this:

Name	Total Transactions	Budget	Budget Remaining
John Appleseed	$305	$1,000	$695
David Smith	$1,070	$2,000	$930
Bill Gates	$1,504,513	$2,000,000	$495,487

Any help would be appreciated.

Thank you

[Anonymous]

To anyone else who finds this post, @MFelix helped me out over at this post.

[MFelix · Accepted solution]

Hi @Anonymous ,

You need to create a relationship between both tables, and then create the following measure:

Budget Remaining = SUM(Budget[Budget])-SUM(Purchases[Transaction Amount])

Now create your matrix accordingly to the following:

• Rows: Name (Budget Table)
• Values:
  • Budget (Budget Table)
  • Transaction Amount (Transaction Table)
  • Budget remaining (Measure created previously)

Check the result in the PBIX file attach (August version).

Regards,

MFelix

[Anonymous]

Hi @MFelix ! Thank you very much for this solution, and it has worked just as you stated.

However I am expierencing a new issue that I should have disclosed initially. I have added dates to the transaction table, and this is how Power BI looks when added.

	January			February			March			Total
	Transaction Amount	Budget	Remaining Budget	Transaction Amount	Budget	Remaining Budget	Transaction Amount	Budget	Remaining Budget	Transaction Amount	Budget	Remaining Budget
John Appleseed	175	1000	825	60	1000	940	70	1000	930	305	1000	695
David Smith	558	2000	1442	143	2000	1857	369	2000	1631	1070	2000	930
Bill Gates	1007	2000000	1998993	3086	2000000	1996914	1500420	2000000	499580	1504513	2000000	495487

I would prefer that the Budget and Remaining Budget be removed from the monthly sets and to only show the budget and remaining budget columns at the end, as pictured below:

	January	February	March	Total
	Transaction Amount	Transaction Amount	Transaction Amount	Transaction Amount	Budget	Remaining Budget
John Appleseed	175	60	70	305	1000	695
David Smith	558	143	369	1070	2000	930
Bill Gates	1007	3086	1500420	1504513	2000000	495487

Is this possible in a Power BI matrix?

Thank you for your help.