Adding calculation as rows in Matrix

[PBIRookieAZ]

Hi PBI Expoerts, 

I have the table in blue box as a matrix visual in PBI. I need to add two rows for GP and GM% calculations at the bottom of the matrix. How can I do that? TIA!!

[v-dineshya · Accepted solution]

Hi @PBIRookieAZ ,

Thanks for reaching out to the Microsoft fabric community forum. 

Solution 1: Using DAX Measures

Create Measures for GP and GM%: Go to the Modeling tab & Click on New Measure.

DAX formulas:

Gross Profit (GP): GP = SUM('Sales'[Revenue]) - SUM('Sales'[Cost of Goods Sold])

Gross Margin % (GM%): GM% = DIVIDE([GP], SUM('Sales'[Revenue]), 0)

Add Measures to the Matrix: In your matrix visual, drag the GP and GM% measures into the Values field.

Show as Total Row: In the matrix settings, under Format → Subtotals, ensure that subtotals are enabled if needed.

Solution 2: Create a Calculated Table:

Summary Table =
UNION (
SELECTCOLUMNS('Sales', "Category", 'Sales'[Category], "Value", [GP]),
SELECTCOLUMNS('Sales', "Category", "GM%", "Value", [GM%])
)

Alternative code:

SummaryTable =
UNION(
SUMMARIZE(
'Sales',
'Sales'[Category],
"GP", [GP],
"GM%", [GM%]
),
ROW(
"Category", "Total",
"GP", [GP],
"GM%", [GM%]
)
)

Add the Custom Table to the Matrix Visual: Replace your original table with the SummaryTable in the matrix visual. This table will include the GP and GM% calculations at the bottom.

Format the Matrix Visual: Adjust the formatting of the matrix visual to ensure that the new rows for GP and GM% are clearly visible and distinguishable from the other rows.

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

[v-dineshya]

Hi @PBIRookieAZ ,

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

[v-dineshya]

Hi @PBIRookieAZ ,

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

[v-dineshya]

Hi @PBIRookieAZ ,

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

[lbendlin]

You can't really do that unless you go full custom matrix.  Better to use a separate visual as you are doing.