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.