SUM excluding IDs from Other column in the same table

[Anonymous]

Hi all,

Maybe this is super simple but I just can't figure it out. I have a slicer with multi selection available, when a single Country is selected it should SUM everything in Values just normal but when more than one country is selected I want to aggregate only if the country id doesn't exist in another column call ReferenceCountryID. Keep in mind that my slicer is using column CountryName and not CountryID.

Here is a summary of data and expected results to make it easy to understand:

I tried with ALLEXCEPT, VALUES, etc and nothing.

[Anonymous]

This is really cool, thank you for investing time into my question. Much appriciate it.

I found another solution that also works in case anybody has a similar issue:

=VAR CountryList = SUMMARIZE(ALLSELECTED(Facts),Facts[CountryId])
VAR Result = CALCULATE (
SUM([Values])
,FILTER(Facts, NOT(Facts[ReferenceCountryID] IN CountryList ))
)

RETURN
IF(Result = 0, BLANK(),Result)

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please check the attachment.

1.Create a seperate countryname table.

2.Create two measures.

flag = 
VAR _allID =
    SUMMARIZE (
        FILTER (
            ALL ( 'Table' ),
            'Table'[CountryName] IN ALLSELECTED ( 'Table 2'[CountryName] )
        ),
        'Table'[CountryID]
    )
VAR _flag =
    IF (
        MAX ( 'Table'[ReferenceCountryID] )
            IN _allID
                && MAX ( 'Table'[CountryName] ) IN ALLSELECTED ( 'Table 2'[CountryName] ),
        SUM ( 'Table'[Values] )
    )
RETURN
    _flag

Output = 
VAR _countSele =
    COUNTX (
        FILTER ( 'Table', 'Table'[CountryName] IN ALLSELECTED ( 'Table'[CountryName] ) ),
        [CountryName]
    )
VAR _sum =
    CALCULATE (
        SUM ( 'Table'[Values] ),
        FILTER (
            'Table',
            'Table'[CountryName] IN ALLSELECTED ( 'Table 2'[CountryName] )
        )
    )
RETURN
    IF ( _countSele = 1, _sum, _sum - SUMX ( 'Table', [flag] ) )

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.