Forum Discussion
Solved
Power BI desktop create a Calculated Row in Matrix Visualisation
Hi guys, I'm new to PBI Desktop and DAX functions and I'd like to create a calculated row with columns which are the result of subtracting two previous columns. Here is my dataset.
| Budget Type | Amount 1 | Amount 2 | Amount 3 | Amount 4 |
| Gross | 50000 | 30000 | 20000 | 15000 |
| Spend | 5000 | 3000 | 2000 | 1500 |
And I'd the Matrix Visualisation to look like.
| Budget Type | Amount 1 | Amount 2 | Amount 3 | Amount 4 |
| Gross | 50000 | 30000 | 20000 | 15000 |
| Spend | 5000 | 3000 | 2000 | 1500 |
| Net | 45000 | 27000 | 18000 | 13500 |
So the Net = Gross - Spend. Is there an easy wasy to achieve this?
Many Thanks.
Please try to create two tables with following DAX formulas.
NetTable = SUMMARIZE( Table1, "Budget", "Net", "Amount1", CALCULATE( SUM( Table1[Amount1] ), Table1[Budget Type] = "Gross" ) - CALCULATE( SUM( Table1[Amount1] ), Table1[Budget Type] = "Spend" ), "Amount2", CALCULATE( SUM( Table1[Amount2] ), Table1[Budget Type] = "Gross" ) - CALCULATE( SUM( Table1[Amount2] ), Table1[Budget Type] = "Spend" ), "Amount3", CALCULATE( SUM( Table1[Amount3] ), Table1[Budget Type] = "Gross" ) - CALCULATE( SUM( Table1[Amount3] ), Table1[Budget Type] = "Spend" ), "Amount4", CALCULATE( SUM( Table1[Amount4] ), Table1[Budget Type] = "Gross" ) - CALCULATE( SUM( Table1[Amount4] ), Table1[Budget Type] = "Spend" ) )Table2 = UNION( Table1, NetTable )
Best Regards,
Herbert
2 Replies
Please try to create two tables with following DAX formulas.
NetTable = SUMMARIZE( Table1, "Budget", "Net", "Amount1", CALCULATE( SUM( Table1[Amount1] ), Table1[Budget Type] = "Gross" ) - CALCULATE( SUM( Table1[Amount1] ), Table1[Budget Type] = "Spend" ), "Amount2", CALCULATE( SUM( Table1[Amount2] ), Table1[Budget Type] = "Gross" ) - CALCULATE( SUM( Table1[Amount2] ), Table1[Budget Type] = "Spend" ), "Amount3", CALCULATE( SUM( Table1[Amount3] ), Table1[Budget Type] = "Gross" ) - CALCULATE( SUM( Table1[Amount3] ), Table1[Budget Type] = "Spend" ), "Amount4", CALCULATE( SUM( Table1[Amount4] ), Table1[Budget Type] = "Gross" ) - CALCULATE( SUM( Table1[Amount4] ), Table1[Budget Type] = "Spend" ) )Table2 = UNION( Table1, NetTable )
Best Regards,
Herbert
