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 ha...
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
    )
)
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.

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

    Ashish_Mathurthanks.

     

    Sorry, but i didnt write the complete formula.

     

    I already tried with

     

    name of the table: public tuten_booking

     

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

     

    It appears this message "a function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed"

     

    I need a formula like this:

     

    Calculate the total of professional (distinctcount) whos domain<>"easy" or "sodimac" AND the AVERAGE of ALL of their works is <= 3,8

     

    In the table I added before, there are 4 professional, I need a measure that show me only that number.

     

    Thanks!

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Icon for Community Champion rankCommunity Champion
        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
    )
)
    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      8 years ago

      Hi,

       

      Try this

       

      =CALCULATE(COUNT('public tuten_booking'[tuten_user_professional]),FILTER('public tuten_booking','public tuten_booking'[domain]<>"easy"&&'public tuten_booking'[domain]<>"sodimac"&&[average]<=3,8))

       

      Does this work?