Forum Discussion

omarevp's avatar
omarevp
Icon for Helper II rankHelper II
8 years ago
Solved

Distinctcount considering Average and some filters

Hi guys,

 

I know this should be easy, but I need help.

 

I have a list of professionals who have different number of works, every work has a "Score" depending on their performance; also, they have works on different "domains" which are the place where they do the job and "Roles" which are the type of jobs. I need to know IN MEASURE the number of professional who have LOW SCORE IN AVERAGE and add the filter by domain in the same measure. I did it with a table and applied the filters. This is the result:

 

As you can see, the total of professional with low score are 4 (by distinct count). That is the number I need in measure :(

 

 

 

 

 

 

Need Help
Show and Tell
Tips and Tricks
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    omarevp

     

    Give this a shot

     

    low score pros =
CALCULATE (
    COUNT ( 'public tuten_booking'[tuten_user_professional] ),
    'public tuten_booking'[domain] <> "easy",
    'public tuten_booking'[domain] <> "sodimac",
    FILTER (
        ALLSELECTED ( 'public tuten_booking'[tuten_user_professional] ),
        [average] <= 3.8
    )
)

8 Replies

  • dearwatson's avatar
    dearwatson
    Icon for Continued Contributor rankContinued Contributor
    8 years ago

    Hi omarevp

     

    First lets create your distinct count measure

     

    Professionals = DISTINCTCOUNT(TableName[tuten_user_professional])

     

    If the "average" column is just a column based on the filtering in your screenshot you could get away with a basic calculate I think

     

    Low scoring pros = CALCULATE([Professionals],FILTER(TableName,TableName[average] <= 3.8))

     

    Thats a start at least... let me know how it goes.

     

    Cheers

    Greg

     

    • omarevp's avatar
      omarevp
      Icon for Helper II rankHelper II
      8 years ago

      dearwatsonthanks for your answer.

       

      Nope, "average is not a column, it is a measure I made from "score" column.

       

      I tried to do it like this:

       

      First I created a measure called "average" which is average = AVERAGE(table1[score])

       

      then:

       

      low score pros = CALCULATE(DISTINCTCOUNT(tuten_user_professionals),table1[domain]<>"easy",table1[domain]<>"sodimac", [average]<=3,8)           didnt work using the "average" measure because it says: a function CALCULATE has been used in a True/False expression that is used as a table filter expression. This is not allowed.

       

      then i tried with AVERAGE as a function inside the new measure:

       

      low score pros = CALCULATE(DISTINCTCOUNT(tuten_user_professionals),table1[domain]<>"easy",table1[domain]<>"sodimac", AVERAGE(table1[score]<=3,8)        didnt work too using the function calling the column "score" because it says: a function AVERAGE has been used in a True/False expression that is used as a table filter expression. This is not allowed.

       

      What I need is to add the condition that is has to be: show me the total of professionals that are from "xx" domain, which their average score is <= 3,8, not the score from a specific work, the average score from all their scored works.

       

       

      should be like this: 

       

      If I take the original data with their score per work:

       

      Then, I can set the "average" option for "score" field or i can replace it for the "average" measure and apply the filter average<=3,8 and it goes like this: (it reduces the number of rows, obviously)

       

       

      So far so good, but, I need to know the number of professionals in measure. And which are those professionals?:

       

      [email protected]

      [email protected]

      [email protected]

      [email protected]

       

      TOTAL OF PROFESSIONAL WITH LOW SCORE FROM XXXX DOMAIN: 4 (applying distinctcount)

       

      I hope I could explain myself.

       

      Thanks in advance!!!

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        8 years ago

        Hi,

         

        Try this

         

        =CALCULATE(DISTINCTCOUNT(tuten_user_professionals[column]),FILTER(Table1,(table1[domain]<>"easy"||table1[domain]<>"sodimac")&&[average]<=3.8))

         

        The DISTINCTCOUNT() requires a column reference.  So in the highlighted portion of the formula, specify the column in which you want to do the distinct count.