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 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))
      • 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?