cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RudyWelvaert Regular Visitor
Regular Visitor

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

Accepted Solutions
Highlighted
Super User
Super User

Re: remove total of one column

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
Highlighted
Super User
Super User

Re: remove total of one column

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
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 382 members 4,204 guests
Please welcome our newest community members: