cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## remove total of one column

Hello,

I have a table F_AMOUNTS

Amount per unit = ROUND(F_AMOUNTS[Amount] / F_AMOUNTS[units];2)
Part = F_AMOUNTS[PARTS] & " - " & F_AMOUNTS[units] & " units"

I use a matrix visualisation
Columns: -
Values: Amount, Amount per unit

The Drill mode is On and Drilled down to the lowest level

I want to remove The total value of the second column

Not the subtotals of the second column

This is the result I want:

Thanks

R.W.

1 ACCEPTED SOLUTION
Resident Rockstar

Sure. There is a workaround to accomplish this, since the default Matrix visual doesn't offer the option to calculate subtotals differently.  You can instead use a measure that behaves differently if it is in a grand total vs a single row or a subtotal row.

`Amt/unit for Matrix = IF(    ISINSCOPE(F_AMOUNTS[Heading]) || ISINSCOPE(F_AMOUNTS[PARTS]),     SUM(F_AMOUNTS[Amount per unit]),     BLANK())`

In this example, if the current scope is the Heading or the PARTS, it sums the Amount per unit normally.  If it is neither of these (like when you're in a grand total row), it returns a blank.  You can read more about it here.

Resident Rockstar

Sure. There is a workaround to accomplish this, since the default Matrix visual doesn't offer the option to calculate subtotals differently.  You can instead use a measure that behaves differently if it is in a grand total vs a single row or a subtotal row.

`Amt/unit for Matrix = IF(    ISINSCOPE(F_AMOUNTS[Heading]) || ISINSCOPE(F_AMOUNTS[PARTS]),     SUM(F_AMOUNTS[Amount per unit]),     BLANK())`

In this example, if the current scope is the Heading or the PARTS, it sums the Amount per unit normally.  If it is neither of these (like when you're in a grand total row), it returns a blank.  You can read more about it here.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.