Forum Discussion

sputnik76's avatar
sputnik76
Frequent Visitor
7 years ago
Solved

Syntax for Sum filters

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?

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      sputnik76
      Frequent Visitor
      7 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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