Forum Discussion

viggo71's avatar
viggo71
Frequent Visitor
6 years ago
Solved

COUNT GROUPBY Filter

Hello,   i have the following table:   EvalID      User    Form    Note Max   Result 1              Lea      AA       10                10 1              Lea      BB       20                20 ...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    6 years ago

    viggo71 

     

    Please try the measure below

    Nb eval = CALCULATE(DISTINCTCOUNT(Sheet1[EvalID]),ALLEXCEPT(Sheet1,Sheet1[User]))

Nb form = COUNTROWS(Sheet1)

form success = CALCULATE(COUNTROWS(Sheet1),FILTER(ALLEXCEPT(Sheet1,Sheet1[User]),'Sheet1'[Result]='Sheet1'[Note Max]))

eval success = 
VAR tbl=SUMMARIZE('Sheet1',Sheet1[EvalID],Sheet1[User],"issuccess",sum(Sheet1[Note Max])-sum(Sheet1[Result]))
VAR tbl2=FILTER(tbl,[issuccess]=0)
return COUNTROWS(tbl2)

     

ryan_mayu's avatar
ryan_mayu
Super User
6 years ago

viggo71 

Is this what you want?

Measure 3 = 
VAR a=COUNTROWS('Sheet21 (2)')
var b=CALCULATE(COUNTROWS('Sheet21 (2)'),FILTER('Sheet21 (2)','Sheet21 (2)'[EvalID     ]=1))
return b/a

  • viggo71's avatar
    viggo71
    Frequent Visitor
    6 years ago

    Hi, thanks for helping,

     

    no, i want the pourcentage of success of Lea , Bob, etc based on Eval ID.

     

    Succes only if user get Result = Note Max for each row of same Eval ID.

     

    In this example Lea succed on Eval ID #1 but fail on #2

     

    I hope your understand. Thanks a lot for helping me 🙂

     

     

    • ryan_mayu's avatar
      ryan_mayu
      Super User
      6 years ago

      viggo71 

       

      Why failed on 2? I saw there is one success and one fail for 2. Could you please provide more detailed info or logic?

       

      • viggo71's avatar
        viggo71
        Frequent Visitor
        6 years ago

        2 is only fail because if only one fail exist for an Eval-ID, it fails

         

        Success only if all forms are validated at 100% (result=note max)

         

        What i want in return is :

         

        User      Nb Eval       Nb Forms         Eval success     Form Success

        Lea        2                 4                       1                      3

        Bob       1                 1                       1                      1

         

        Do you know how to find Eval Success ?