Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## One-to-Many Relationship; Matrix Layout

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

1 ACCEPTED SOLUTION
Super User

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

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português

3 REPLIES 3
Anonymous
Not applicable

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

Super User

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

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português

Anonymous
Not applicable

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.

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors