remove total of one column

[Anonymous]

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

I added two calculated columns

Amount per unit = ROUND(F_AMOUNTS[Amount] / F_AMOUNTS[units];2)

Part = F_AMOUNTS[PARTS] & " - " & F_AMOUNTS[units] & " units"

I use a matrix visualisation

Rows: Part, Heading

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.

[Cmcmahan · Accepted solution]

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.