Forum Discussion
Help needed with DistinctCount & Average
Dear All,
I want to create a card that shows the;
- Count number of Employee who has less than Average Sales of $100 for Client from Category Main (Refer to table below)
- 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!
6 Replies
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!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?
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.
