cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RudyWelvaert
Helper V
Helper V

remove total of one column

Hello,

 

I have a table F_AMOUNTS

 

HeadingPARTSAmountunits
Heading 1Part 1164.359,325,27
Heading 2Part 1174.287,465,27
Heading 3Part 191.490,225,27
Heading 4Part 1170.542,865,27
Heading 5Part 1145.656,665,27
Heading 6Part 1109.116,845,27
Heading 1Part 2117.873,523,48
Heading 2Part 297.241,043,48
Heading 3Part 288.716,043,48
Heading 4Part 295.003,163,48
Heading 5Part 260.206,603,48
Heading 6Part 2118.863,603,48
Heading 1Part 359.339,141,65
Heading 2Part 349.168,861,65
Heading 3Part 354.748,821,65
Heading 4Part 345.641,201,65
Heading 5Part 339.527,101,65
Heading 6Part 348.561,781,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
Part_01.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

Part_02.jpg

 

I want to remove The total value of the second column

Not the subtotals of the second column

This is the result I want:Part_03.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Thanks

 

R.W.

1 ACCEPTED SOLUTION
Cmcmahan
Resident Rockstar
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.

View solution in original post

1 REPLY 1
Cmcmahan
Resident Rockstar
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.

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

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

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors