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!