Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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.
Solved! Go to 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.
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.