COUNT GROUPBY Filter

[viggo71]

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 😉

[ryan_mayu]

@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]

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]

@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]

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 ?

[ryan_mayu · Accepted solution]

@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)

[viggo71]

Thank you Ryan, it works fine 🙂

I really appreciate your help 🙂

[amitchandak]

@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.