Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |