Help needed with DistinctCount & Average

[ngiam]

Dear All,

I want to create a card that shows the;

1. Count number of Employee who has less than Average Sales of $100 for Client from Category Main (Refer to table below) 
2. Count number of Employee who has less than Average Sales of $100 for Client from Category Secondary (Refer to table below) 
   I've created a slicer that allows me to selecte between Main or Secondary

Sales Table

Employee Name	Client	Sales
Ray	C1	100
Ray	C2	150
Ray	C3	125
Bob	C1	120
Bob	C2	100
Bob	C4	100
Cat	C5	150
Cat	C2	160
Cat	C4	175

Client Table

Client	Category
C1	Main
C2	Main
C3	Main
C4	Secondary
C5	Secondary
C6	Secondary

Am currently trying this out ; 

Filter = CALCULATE(

DISTINCTCOUNT('Sales Table'[Name]),

AVERAGE('Sales Table'[Sales] <100, 

FILTER('Client Table', 'Client Table'[Category]="Secondary" || 'SkillLibrary'[Category]="Main"))

)

But doesn't seems to work for me.

Appreciate some hints to help me with this!

[ValtteriN]

Hi,

Your formula was quite close. This counts the number of employees with whose average sales are less than 100 (customerCategory = "Main"):

AverageTest = calculate(DISTINCTCOUNT(Sales[Employee Name]),

filter(Sales,AVERAGEX(FILTER(Sales,RELATED(ClientCategory[Category])="Main"),Sales[Sales ])<100))


For secondary count replace "Main" with "Secondary".

I hope this helps and if it does consider accepting this as a solution and giving the post a thumbs up!

[ngiam]

Thank you very much for your help!

I seem to have this as an error

"The column 'ClientCategory[Category]' either doesn't exist or doesn't have a relationship to any table available in the current context."

Anything that I should be looking out for?

[ValtteriN]

Hi,

Check the table name, I used a slightly different one than the one in your example. (Client Table -> ClientCategory). Additionally, I had a relationship between the Sales and client table.

[ngiam]

Thank you ValtteriN,

Am using the right table. Do many-many relationship has any impact to this?

Thanks!

[ValtteriN]

Yeah, M:N will break RELATED. Here is a formula that works with that relation:

AverageTest =

calculate(DISTINCTCOUNT(Sales[Employee Name]),

filter(Sales,AVERAGEX(

SUMMARIZE(filter(Sales,Sales[Client] in SUMMARIZE(FILTER('ClientCategory',ClientCategory[Category]="Main"),ClientCategory[Client])),Sales[Employee Name],Sales[Client],"AV",AVERAGE(Sales[Sales ]))

,Sales[Sales ])<100))

[ngiam]

Thank you! This seems to work!

That said, it seems to work well for non-decimal numbers and doesn't seems to be workable for number with decimal. Is there a way to enable average of numbers with decimal?