Distinct Count

[Anonymous]

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]

Project	Number Coll Org	Number PP	Av Coll Org	Av PP
0f2	92	8	92	8
ac1	39	10	39	10
f3a	68		68
83b	52		52
Total	225	18	56	5

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!

[Anonymous]

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.

[Vera_33]

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

[Anonymous]

Hi Vera, your message is in blank.

Thanks

[Ashish_Mathur]

Hi,

Share a dataset and show the expected result.

[Vera_33 · Accepted solution]

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]

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: 

[Vera_33]

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]

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.

[amitchandak]

Refer:

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

[Anonymous]

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.