Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

SUM excluding IDs from Other column in the same table

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.

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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)