Forum Discussion
One-to-Many Relationship; Matrix Layout
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 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.