cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
omarevp Regular Visitor
Regular Visitor

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 Smiley Sad

 

Captura.PNG

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Distinctcount considering Average and some filters

@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 8
Highlighted
dearwatson Member
Member

Re: Distinctcount considering Average and some filters

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

 

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte
omarevp Regular Visitor
Regular Visitor

Re: Distinctcount considering Average and some filters

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

Captura.PNG

 

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)

 

Captura.PNG

 

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

 

annelk37@gmail.com

irenemarisol1@gmail.com

jorgeleonardo2906@hotmail.com

parra.electrico2702@gmail.com

 

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

 

I hope I could explain myself.

 

Thanks in advance!!!

Super User
Super User

Re: Distinctcount considering Average and some filters

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 Regular Visitor
Regular Visitor

Re: Distinctcount considering Average and some filters

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

omarevp Regular Visitor
Regular Visitor

Re: Distinctcount considering Average and some filters

@Ashish_Mathur@dearwatson

 

I would really really appreciate if you guys can help me.

 

 

Super User
Super User

Re: Distinctcount considering Average and some filters

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

Re: Distinctcount considering Average and some filters

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?

v-jiascu-msft Super Contributor
Super Contributor

Re: Distinctcount considering Average and some filters

Hi @omarevp,

 

Please pay attention to your privacy. Mask the private data.

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 244 members 2,499 guests
Please welcome our newest community members: