Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Distinct Count

Hi,   I couldn't see an answer in the forum... so here it goes. Thanks in advance.   I have a list of projects (ProjectID) in my table Projects-all. Each of them has different organisations (Orga...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi, so Total and Average are all not correct? Try this:

     

    Number Coll Org=

    COUNTROWS(GROUPBY(FILTER('projects-all','projects-all'[Type]="COLLAB_ORG"),'projects-all'[Organisationid],'projects-all'[projectid]))

     
    Number PP=
    COUNTROWS(GROUPBY(FILTER('projects-all','projects-all'[Type]="PP_ORG"),'projects-all'[Organisationid],'projects-all'[projectid]))
     
    Av COLLAB_ORG =
    VAR NumProject=COUNTROWS(GROUPBY(FILTER('projects-all','projects-all'[Type]="COLLAB_ORG"),'projects-all'[projectid]))
    RETURN
    DIVIDE([Number Coll Org],NumProject)
     
    Av PP =
    VAR NumProject=COUNTROWS(GROUPBY(FILTER('projects-all','projects-all'[Type]="PP_ORG"),'projects-all'[projectid]))
    RETURN
    DIVIDE([Number PP],NumProject)
     
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi, so Total and Average are all not correct? Try this:

 

Number Coll Org=

COUNTROWS(GROUPBY(FILTER('projects-all','projects-all'[Type]="COLLAB_ORG"),'projects-all'[Organisationid],'projects-all'[projectid]))

 
Number PP=
COUNTROWS(GROUPBY(FILTER('projects-all','projects-all'[Type]="PP_ORG"),'projects-all'[Organisationid],'projects-all'[projectid]))
 
Av COLLAB_ORG =
VAR NumProject=COUNTROWS(GROUPBY(FILTER('projects-all','projects-all'[Type]="COLLAB_ORG"),'projects-all'[projectid]))
RETURN
DIVIDE([Number Coll Org],NumProject)
 
Av PP =
VAR NumProject=COUNTROWS(GROUPBY(FILTER('projects-all','projects-all'[Type]="PP_ORG"),'projects-all'[projectid]))
RETURN
DIVIDE([Number PP],NumProject)
 
Anonymous's avatar
Anonymous
Not applicable
6 years ago

HI,

 

Thanks for this! It did work for the first three measures. However, when I entered the formula for Av PP, Av Coll Org gave an error: 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Can you show some rows of your dataset (randomly generated data is ok, just keep the columns)? I made up some rows and all measures were working...

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi both,

       

      I just rebooted the computer and now all measures are working. Thanks for the help!

      In the following days I will be creating more Av measures...  so I hope that what I learnt with your help could be replicated for these.

       

      Best.