Group Summarized values doesn't return correct total

[msantillan]

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

[ImkeF]

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]

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

[ImkeF]

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?

[msantillan]

@ImkeF  If you could see the first picture, it has a negative value on the cash group. Once I made the summarized measure, it was gone on the second picture. But the total still shows the one with the negative value (same with the first picture).

[ImkeF]

Hi @msantillan  

include both fields from the lowest granularity-level in your visual in the SUMMARIZE.

The totals still include the negative value, because the column-values are still summed up.

[msantillan]

Can you please give an example where the column is also included in the Summarized measure @ImkeF ? Thank you.

[ImkeF · Accepted solution]

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.

[msantillan]

@ImkeF  Thanks