topic Sum of a Measure per category in DAX Commands and Tips

Sum of a Measure per category

frankvb — Fri, 18 Dec 2020 09:57:16 GMT

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!

Re: Sum of a Measure per category

amitchandak — Fri, 18 Dec 2020 10:23:03 GMT

frankvb , Try like

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

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

Re: Sum of a Measure per category

frankvb — Fri, 18 Dec 2020 10:40:04 GMT

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.

Re: Sum of a Measure per category

frankvb — Fri, 18 Dec 2020 11:02:28 GMT

amitchandak

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