cancel
Showing results for
Did you mean:
Helper V

## remove total of one column

Hello,

I have a table F_AMOUNTS

 Heading PARTS Amount units Heading 1 Part 1 164.359,32 5,27 Heading 2 Part 1 174.287,46 5,27 Heading 3 Part 1 91.490,22 5,27 Heading 4 Part 1 170.542,86 5,27 Heading 5 Part 1 145.656,66 5,27 Heading 6 Part 1 109.116,84 5,27 Heading 1 Part 2 117.873,52 3,48 Heading 2 Part 2 97.241,04 3,48 Heading 3 Part 2 88.716,04 3,48 Heading 4 Part 2 95.003,16 3,48 Heading 5 Part 2 60.206,60 3,48 Heading 6 Part 2 118.863,60 3,48 Heading 1 Part 3 59.339,14 1,65 Heading 2 Part 3 49.168,86 1,65 Heading 3 Part 3 54.748,82 1,65 Heading 4 Part 3 45.641,20 1,65 Heading 5 Part 3 39.527,10 1,65 Heading 6 Part 3 48.561,78 1,65

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

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks