cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RD2019 Frequent Visitor
Frequent Visitor

How to stop summarizing specific fields in Matrix visual

Hi All,

 

I have the below matrix in the exact same format that I need.

 

image 1.jpg

 

Here, 'Labels' is a text field - both 'Labels' and 'Rate' I don't want to get summarized (as highlighted).

 

My fields pane -

 

image 2.jpg

 

This is a transactional date and there can be multiple occurances of the same product (hence the same Labels and rates) under each type - so using HASONEVALUE would not be of much help I think.

 

Please can someone help here?

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

Re: How to stop summarizing specific fields in Matrix visual

Hi @RD2019 

Create measures

Measure_column3 =
IF (
    ISINSCOPE ( 'Table'[column2] ),
    MAX ( 'Table'[column3] ),
    IF ( ISINSCOPE ( 'Table'[column1] ), BLANK (), BLANK () )
)

Measure_column4 =
IF (
    ISINSCOPE ( 'Table'[column2] ),
    SUM ( 'Table'[column4] ),
    IF ( ISINSCOPE ( 'Table'[column1] ), BLANK (), BLANK () )
)

Capture10.JPG

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

RD2019 Frequent Visitor
Frequent Visitor

Re: How to stop summarizing specific fields in Matrix visual

Hi Maggie,

 

Thanks for this...I think this would work as well. However I have applied a work-around using HASONEVALUE only. As in my case, every product has the same label code and rate - I have just use an IF condition stating that if there is one value for any label or rate (although there can be multiple occurances) - just take the AVERAGE value, otherwise BLANK (ensuring the subtotal will be blank). Seems that it's working fine now.

View solution in original post

4 REPLIES 4
Super User
Super User

Re: How to stop summarizing specific fields in Matrix visual

I see repeating values, so assuming these are both columns, you can write measures as follows

 

=selectedvalue(table[column])



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Community Support Team
Community Support Team

Re: How to stop summarizing specific fields in Matrix visual

Hi @RD2019 

Create measures

Measure_column3 =
IF (
    ISINSCOPE ( 'Table'[column2] ),
    MAX ( 'Table'[column3] ),
    IF ( ISINSCOPE ( 'Table'[column1] ), BLANK (), BLANK () )
)

Measure_column4 =
IF (
    ISINSCOPE ( 'Table'[column2] ),
    SUM ( 'Table'[column4] ),
    IF ( ISINSCOPE ( 'Table'[column1] ), BLANK (), BLANK () )
)

Capture10.JPG

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

meet2aftab Frequent Visitor
Frequent Visitor

Re: How to stop summarizing specific fields in Matrix visual

Did you tried to move “First Labels” and “Rate” from Values to Rows? You are getting total for text fields because you have placed these text fields in value section of matrix. see below screenshot

visual.jpg

RD2019 Frequent Visitor
Frequent Visitor

Re: How to stop summarizing specific fields in Matrix visual

Hi Maggie,

 

Thanks for this...I think this would work as well. However I have applied a work-around using HASONEVALUE only. As in my case, every product has the same label code and rate - I have just use an IF condition stating that if there is one value for any label or rate (although there can be multiple occurances) - just take the AVERAGE value, otherwise BLANK (ensuring the subtotal will be blank). Seems that it's working fine now.

View solution in original post

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)