Forum Discussion

msantillan's avatar
msantillan
Helper II
6 years ago
Solved

Group Summarized values doesn't return correct total

Hi, Can somebody please help and explain why this doesn't return the right total values.

 

I originally have this matrix with flat values:

 

However, business does not want the negative value to be included in the totals, so I created this measure: 

 

TRY new new = SUMX(SUMMARIZE('AUM Config', 'AUM Config'[IDR_BILLING_GROUP], "@MKT", SUM('AUM Config'[MKT_VALUE_USD])), IF( [@MKT] > 0, [@MKT], BLANK()))
 
It looks right seen in the new matrix below, but it still has the same totals as above even if the negative is missing anymore:

 
Please help. Thanks

 

Need Help
  • ImkeF's avatar
    ImkeF
    6 years ago

    Hello

    this would probably look like this:

    Test again ?
    SUMX (
    SUMMARIZE (
    'AUM Config',
    'AUM Config'[IDR_BILLING_GROUP], - don't take this from the fact table
     'YourDimTableName'[IDR Config SETTINGNA....], // take the field from the Dim table that you have used in the report
    'YourDimTableName'[Adjusted MOD_Groupin....], // take the field from the Dim table that was used in the report
    "@MKT", SUM ( 'AUM Config'[MKT_VALUE_USD] )
    ),
    SI ( [@MKT] > 0, [@MKT], WHITE () )
    )

    Replace the red text with the model match fields.

8 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    6 years ago

    Hi msantillan  

    check the granularity of the field "AUM Config'[IDR_BILLING_GROUP]"

    In addition I'd suggest to replace it with a field from your dimension table instead. Just make sure that it is a finer grain that the field you drag into the visual.

     

     

    • msantillan's avatar
      msantillan
      Helper II
      6 years ago

      ImkeF  But I need it to be filtered on a row basis level, can you please expound more on this?

       

      • ImkeF's avatar
        ImkeF
        Community Champion
        6 years ago

        Hi msantillan 

        if you want it to be filtered on a row level, then the field "AUM Config'[IDR_BILLING_GROUP]" would have to have unique values. (Just the name suggests that it doesn't).

         

        But it seems that I haven't read your question properly:

        What exactly do you mean with: "business does not want the negative value to be included in the totals,"?

        Could you please give samples (before and after) for the data you've provided?