count of certain values per group

[uniqum47]

Hi, it sounds like such a simple problem, but I am stuck with it and tried several solutions offered on the forum with no luck.

I have following DevOps data  of related  parent and children in one table (yeah not the best, but decided to leave it in one table since data is not heavy). It is one to many relaitonships between 1 source to many targets. 

I need a measure that will give me how much state Value (closed) is per each Source Item (i.e.  for source #1 Closed = 1, for source #2  - o, per #3 - 2.

At the end, I need it to calculate the % of closed children under each parent, which I can do (using countr of children)once figure out the measure per state. 

Your help is Very much appreciated

SourceWorkItemID	State	TargetWorkItemID	Count of Children
1	New	11	3
1	Closed	12	3
1	Active	13	3
2	New	21	2
2	New	22	2
3	Closed	33	2
3	Closed	32	2

[Fowmy · Accepted solution]

@uniqum47 

I made a measure to calculate the %, it will work at the total level as well.

% closed Children = 
var __Closed = 
    SUMX(
        VALUES(Table2[SourceWorkItemID]),
        CALCULATE(
            COUNT(Table2[State]),
            Table2[State] = "Closed"
        )
    )
var __Count = 
    SUMX(
        VALUES(Table2[SourceWorkItemID]),
        CALCULATE( COUNT(Table2[State]) )
    )
var __result = 
    DIVIDE( __Closed , __Count )
return
    __result

[uniqum47]

thank you  - that helped a lot.

Can I stretch my luck and ask for further help - the requirement has changed  -if items are Closed, they are not necessarily released to prod. I have another column with release date mixed with null, I transfered it to 1 - not released, 2 released.

I tried to adjust your query, but it doesnt adjust to count all children with 1 and 2. I need it to group, then count how many 2 are in the group anf give me % of 2 per group (i.e. group 2 is 50%).

I feel like I am missing something basic 😞

Many many thanks!

SourceWorkItemID	State	TargetWorkItemID	Count of Children	release
1	New	11	3	1
1	Closed	12	3	2
1	Active	13	3	2
2	New	21	2	2
2	New	22	2	1
3	Closed	33	2	1
3	Closed	32	2	2