How to calculate SUM with IF statement and CONTAIN

[punksterz626]

Hello Team,

Hope everyone is doing well. I am hoping someone can assist me with writing a measure that satisfies these conditions. 

I have a table, see below, that has multiple columns, project number, name, and percentage value. 

I want to sum the total percentage for any given name; however, with one caveat. I have a list of names that I want to automatically add 40% on top of what the total would be.

For example, John (20,10,20) and Ana (40,20) are on this list. Normally their number would sum as 50 (Andy) and 60 (Ana), but since they're on this list their total would be 90 for Andy and 100 for Ana. Is this achievable?

Project Num  Name              Percentage   Result I want (sum of percentage)

111	John	20	90
123	Andy	10	30
214	John	10	90
915	Chris	10	20
852	Jason	30	60
741	Ana	40	100
159	Steve	20	30
456	John	20	90
789	Andy	20	30
321	Chris	10	20
147	Jason	30	60
258	Ana	20	100
369	Steve	10	30

[v-shex-msft]

Hi @punksterz626,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[AlexisOlson · Accepted solution]

You can multiply the additional 40% with a boolean test of whether the name is Ana or John.

Sum of Percentage =
CALCULATE ( SUM ( Table1[Percentage] ), ALLEXCEPT ( Table1, Table1[Name] ) )
    + 40 * ( SELECTEDVALUE ( Table1[Name] ) IN { "Ana", "John" } )

Edit: As @jgeddes suggests, you can replace a hardcoded list { "Ana", "John" } with a reference to another list like VALUES ( nameTable[Name] ) if you need a more dynamic approach.

[jgeddes]

If there are too many names to hard code into the measure and the names exist on another table you can use

Conditional Sum =

var _sum =

SUMX(

    FILTER(ALL('Table'),'Table'[Name] = SELECTEDVALUE('Table'[Name])),

    'Table'[Percentage]

)

var _nameOnList =

IF(

    NOT(ISBLANK(LOOKUPVALUE(nameTable[Name],nameTable[Name],SELECTEDVALUE('Table'[Name])))),

    TRUE(),

    FALSE()

)

Return

IF(

    _nameOnList,

    _sum + 40,

    _sum

)

[jgeddes]

The measure

Conditional Sum =

var _sum =

SUMX(

    FILTER(ALL('Table'),'Table'[Name] = SELECTEDVALUE('Table'[Name])),

    'Table'[Percentage]

)

Return

IF(

    SELECTEDVALUE('Table'[Name]) = "John" || SELECTEDVALUE('Table'[Name]) = "Ana",

    _sum + 40,

    _sum

)

gets me the result