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 (OrganisationID) and a colum specifying the role (Column "TYPE", values: COLLAB_ORG, PP_ORG). The ultimate goal is to know the average number of organisations per Type. I used the following measures (different formulas to test results):

 

Number Coll Org: 

CALCULATE(DISTINCTCOUNT('projects-all'[Organisationid]),'projects-all'[Type]="COLLAB_ORG")
Number PP:
CALCULATE(DISTINCTCOUNT('projects-all'[Organisationid]),'projects-all'[Type]="PP_ORG")
Number projects = DISTINCTCOUNT('projects-all'[projectid])
Av COLLAB_ORG = [Number Coll Org]/[Number projects]
Av PP = [Number PP]/[Number projects]
 
 
ProjectNumber Coll OrgNumber PPAv Coll OrgAv PP
0f2928928
ac139103910
f3a68 68 
83b52 52 
Total22518565

 

Problems:

- Total Coll Org and Total PP are not the sum of distintc organisationsID (type COLL_ORG or PP) for each project. The total should reflect the sum of distinct values for each project. I tried several formulas (SUMX, CALCULATETABLE, etc) but nothing. 

- Total Av Coll Org and Total Av PP. The totals (previous bullet point) should only divided by the number of projects that have total. For instance, Total Av Coll PP should be 8.5 ( (8+9)/2) instead of 4 ((8+9)/4). I tried AVERAGE, but couldn't make it work.

 

Thanks!

 

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)
     

10 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Refer:

    sumx(summarize('projects-all','projects-all'[project Id],"_dis",[Number PP]),[_dis])

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi,

       

      Thanks for your reply.

       

      However, I think that DAX is missing brackets? What Dis means? (it is not recognise).

      Is this measure for the averages or for the totals?

       

      Thanks.

  • 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 all,

     

    I hope you're feeling fine.

     

    I could manage to generate the average for a lot of dimensions. But, I couldn't find any DAX formula that couldn't help me with the challenge I have. 

     

    Here's a link to an excel file of projects I want to analyse. As you can see, each has a number of research topics and research subjects. Each project could address the same topics though. I have a massive list of Research Topics and Subjects that any project (project table) could address. So now, my aim is to provide a list of Research Topics and Research Subjects that filtered projects don't address. Each table "Resarch Topic" and "Research Subject" provide a column for the name and another for the ID (provided in the excel file). Ideally, there should be a DAX formula that will compare the Research Topic/Subject ID from each project (first excel tab) against the list of IDs I have in tables "Research Topic" and "Research Subject", and return a list of Research Topics and Subjects that projects don't cover. Then, the PBI desktop should show a table with these results.

     

    I hope the excel file works fine. The pbi file is 200mb and I don't know how to cut it down to only show what I just explained. I've also attached the data model. I know it might not look "efficient", but this is what I come up to avoid manytomany relatioships if I used "projects" table.

     

    Thanks again so much for your help.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      sorry...the internet issue...I mean could you please show what you want? Use Excel to put some ideal result

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago
        Hi Vera, your message is in blank.

        Thanks