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

2              Lea      AA       10                10

2              Lea      CC       40                39

3              Bob     CC       10                10

 

 

and i want to display that Lea got 50% success (Eval 1 = true, Eval 2 = false)

 

What mesure can do it ?

 

Thanks for your help, i'm new here 😉

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)

     

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    viggo71 , your data and logic not in Sync. The information you have provided is not making the problem clear to me. Can you please explain with an example.

    Appreciate your Kudos.


     

  • 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?