Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
berta2b
Helper I
Helper I

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 TypeAmount 1Amount 2Amount 3Amount 4
Gross50000300002000015000
Spend5000300020001500

 

And I'd the Matrix Visualisation to look like.

 

Budget TypeAmount 1Amount 2Amount 3Amount 4
Gross50000300002000015000
Spend5000300020001500
Net45000270001800013500

 

So the Net = Gross - Spend. Is there an easy wasy to achieve this?

 

Many Thanks.

1 ACCEPTED SOLUTION
v-haibl-msft
Employee
Employee

@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" )
)

Power BI desktop create a Calculated Row in Matrix Visualisation_1.jpg

 

Table2 = UNION( Table1, NetTable )

Power BI desktop create a Calculated Row in Matrix Visualisation_2.jpg

 

Best Regards,

Herbert

View solution in original post

2 REPLIES 2
v-haibl-msft
Employee
Employee

@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" )
)

Power BI desktop create a Calculated Row in Matrix Visualisation_1.jpg

 

Table2 = UNION( Table1, NetTable )

Power BI desktop create a Calculated Row in Matrix Visualisation_2.jpg

 

Best Regards,

Herbert

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

 

@berta2b

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.