Forum Discussion
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
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
3 Replies
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
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.
To anyone else who finds this post, MFelix helped me out over at this post.