Forum Discussion

ss89's avatar
ss89
Icon for Helper II rankHelper II
3 years ago

Count rows according to multiple conditions

Hello Community,

 

I need to calculate how many deviations were closed on time according to the target date within the relevant month. I have "opened date", "target date" and "closed date" columns.

 

KPI= (deviations closed/deviations opened in the relevant month) 

 

Many thanks in advance for your support

Need Help

8 Replies

  • Arul's avatar
    Arul
    Icon for Super User rankSuper User
    3 years ago

    ss89 ,

    Could you share any sample data? and add some more info in detail.

    Thanks,

    Arul

  • ss89's avatar
    ss89
    Icon for Helper II rankHelper II
    3 years ago

    Hi Arul,

    thanks for your prompt reply.

     

    Please, find attached a screen as an example. Hope this clarify.

     

    Let me know if you need more details.

     

    Thanks in advance for your support.

     

     

     

    • Arul's avatar
      Arul
      Icon for Super User rankSuper User
      3 years ago

      ss89 ,

      try this formula

       

      Delayed = 
VAR _countOfOntime = CALCULATE(
    COUNTA('Item Table'[Item Code]),
    FILTER(
        'Item Table',
        MAX('Item Table'[Closed Date])>MAX('Item Table'[Target Date])
    ))+0
VAR _allCount = CALCULATE(
    COUNTA('Item Table'[Item Code]),
   ALL('Item Table'))
RETURN DIVIDE(_countOfOntime,_allCount)

      use the target column in the matrix visual column field before that please change the format of target column like the below,

       

       

      result:

      Thanks,

      Arul

      • ss89's avatar
        ss89
        Icon for Helper II rankHelper II
        3 years ago

        Hi Arul,

        if I try to type the first formula, the following error message appears:

        A 'FILTER' function was used in a True/False expression used as a table filter expression. This is not allowed.

         

        Could you please support me?

         

        Thanks in advance!