Power BI desktop create a Calculated Row in Matrix Visualisation

[berta2b]

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.

[v-haibl-msft · Accepted solution]

@berta2b

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

[berta2b]

Herbert, thanks so much for your solution. I have tried your suggestion and it works.

@berta2b