Forum Discussion

RD2019's avatar
RD2019
Frequent Visitor
7 years ago
Solved

How to stop summarizing specific fields in Matrix visual

Hi All,

 

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

 

 

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

 

My fields pane -

 

 

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?

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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 () )
)

     

    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.

  • RD2019's avatar
    RD2019
    7 years ago

    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.

4 Replies

  • MattAllington's avatar
    MattAllington
    Community Champion
    7 years ago

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

     

    =selectedvalue(table[column])

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    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 () )
)

     

    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.

    • RD2019's avatar
      RD2019
      Frequent Visitor
      7 years ago

      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.

  • meet2aftab's avatar
    meet2aftab
    Helper I
    7 years ago

    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