Sum of a Measure per category

[frankvb]

Hi,

I am new here, so possibly this is a stupid question.

I want to have a measure that gives me the amount of collaborations between partners on projects

Im working in powerBI via a direct connection to analysis services. That means I can only add measures.

My data looks a bit like this:

project	partner	Status	month
A	X	positive	june
A	Y	positive	june
B	X	negative	september
B	Z	negative	september
B	W	negative	september

so If I have 3 partners, I would have 2 collaborations.

I have to count the number of collaborations per project with the formula:   Σ N*(N-1)/2

Is this possible in a measure? 

I cant really figure out how to make it work so it counts per project AND gives me a proper sum.

What I do right now is the following measure, and then in my report filter by project.

calculate(

(distinctcount(table[partner]) *

(distinctcount(table[partner])-1) / 2),

table[status]= "positive"

)

This gives a correct result per project, but of course my total is off, as it computes the formula for everything, and doesnt add up the numbers of each project.

Thank you in advance!

[frankvb]

@amitchandak 

I checked the results and it looks like exactly what I needed, thanks! 😊

[amitchandak · Accepted solution]

@frankvb , Try like

sumx(values(Table[project]) ,calculate(
(distinctcount(table[partner]) *
(distinctcount(table[partner])-1) / 2),
table[status]= "positive"
))

or

averageX(values(Table[project]) ,calculate(
(distinctcount(table[partner]) *
(distinctcount(table[partner])-1) / 2),
table[status]= "positive"
))

[frankvb]

Thanks amitchandak!

(this post was edited)

Now I am getting some values! I will validate and let you know if it was what i needed.