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,

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

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