Forum Discussion

ngiam's avatar
ngiam
Helper I
4 years ago

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 Employe...
ngiam's avatar
ngiam
Helper I
4 years ago

Thank you ValtteriN,

 

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

 

Thanks!

ValtteriN's avatar
ValtteriN
Community Champion
4 years ago

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's avatar
    ngiam
    Helper I
    4 years ago

    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?