IF function measure

[YBZ]

Hi all,

I am struggling with a measure as I would like to apply an IF condition.

I would like to sum up the costs, but IF Projects WBS starts with 'O.NO.XZZ', it should exclude the cost linked to GL Account A7.

When I however start with my IF function, I would like to refer to the column Project WBS, but it seems that I am only allowed to refer to a measure?

Project WBS	GL account	Cost
O.NO.XZZ.123	A7	100
O.NO.XZZ.124	A8	200
P.NO.XZZ	A7	50
P.NO.XZZ	A7	70

Anybody could guide me to right direction ? 🙂

[v-jayw-msft · Accepted solution]

Hi @YBZ ,

Check this measure:

Measure = SUMX(ALL('Table'),'Table'[Cost])-SUMX(FILTER('Table',LEFT('Table'[Project WBS],8)="O.NO.XZZ"&&'Table'[GL account]="A7"),'Table'[Cost])

Best Regards,

Jay

[YBZ]

thanks, I am actually still struggling with this one. Basically I would need to create the following measure : 

If Recharge project, it should sum up all actuals but EXCLUDE GL account A7995050. Otherwise, just sum up all actuals.

The second condition of my if statement works, but there is a problem with the first part.

Actuals YTD test = if(all('Mapping WBS'[Budget]) = "Recharge",calculate ( sum('Capex OC 1/2/3 Foundation (2)'[Actuals]), filter('current ytd period','current ytd period'[month current ytd period] <= MAX('current ytd period'[month current ytd period])),filter('Capex OC 1/2/3 Foundation (2)','Capex OC 1/2/3 Foundation (2)'[Cost Element (CE).Cost Element (CE) Level 01.Key (Not Compounded)] <> "A7995050"))

 , calculate ( sum('Capex OC 1/2/3 Foundation (2)'[Actuals]), filter('current ytd period','current ytd period'[month current ytd period] <= MAX('current ytd period'[month current ytd period])))))

[jcalheir]

Not sure what measure are you using, but if you want to sum the costs based on the condition you mentioned, do something like:

Your measure = 
CALCULATE(
    SUM('yourtable'[accountCost]),
    FILTER(
        Sales,
        LEFT(Sales[Project WBS],8) <> "O.NO.XZZ"
    )
)

 Kind regards,
José
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂