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...
ValtteriN's avatar
ValtteriN
Community Champion
4 years ago

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'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?