Syntax for Sum filters

[sputnik76]

Am trying to filter one table with data from another related table but the syntax is beating me... new utilisation = FORMAT(DIVIDE(SUM('time_entries'[hours]), FILTER(ALLSELECTED(tasks),'tasks'[task name] = "On Leave")), [Max Possible Hours]), "Percent")

I need to take the sum of time_entries'[hours], filter it by a task called 'On leave' (from another table) and then divide it by another measure called Max Possible Hours. The filter thing is not working out for me. Can anyone help?

[Anonymous]

Hi @sputnik76 ,

Please try below code:

new utilisation = DIVIDE(CALCULATE(SUM('time_entries'[hours]), 'tasks'[task name] = "On Leave"), [Max Possible Hours], 0)

Thanks.

Aiolos Zhao

[sputnik76]

Nearly works. If I want to make filter for everything EXCEPT 'On leave, how would I achieve that?

[Anonymous · Accepted solution]

@sputnik76  do you mean :

new utilisation = DIVIDE(CALCULATE(SUM('time_entries'[hours]), 'tasks'[task name] <> "On Leave"), [Max Possible Hours], 0)

 Please try.

Aiolos Zhao

[sputnik76]

Great thank you!

---

[Anonymous]

@sputnik76 

I'm glad to help you, if my answer is correct, could you please mark it as "accepted solution"?

Thanks.

Aiolos Zhao