Calculating % of child tasks missing and including parent tasks with no child tasks

[jarwest]

Hey all,

I have a table of items that are related to children items. All items are in the same table, so I calculated a secondary table.

For reference, my initial table looks something like this. I've omitted some coumns unused in the calculation.

Key	Category	BigLevelKey	Name	isDone	Status
1	Big	1	A	0	In progress
2	Medium	1	B	1	Done
3	Small	4	C	0	Not Started
4	Big	4	D	0	Not Started
5	Big	5	E	0	Not Started
6	Big	6	F	0	In Progress
7	Medium	6	G	1	Done
8	Medium	6	H	0	In Progress
9	Small	6	I	0	Not Started
10	Small	6	J	0	Not Started
11	Big	11	K	0	In Progress
12	Small	11	L	1	Done
13	Small	11	M	1	Done
14	Small	11	N	1	Done
15	Small	11	O	0	Not Started

With this table in mind, I wanted to add on a column counting up the number of isDone values to get a % complete for each item that was in the Big category.

So, I should have 100% done for A, since it has 1 medium item that is done. I should have 0% for D and E, because D has 1 task that is not done, and E has no tasks.

I came up with the calculated table below, however I am running into an issue where E, and similar rows with no child items, are not showing up in my end result.

TableSummarized = SUMMARIZE( FILTER('Table1', Table1[Category] = "Medium"||Table1[Category] = "Small"), Table1[BigLevelKey], "DoneCount", sum(Table1[isDone]),"CountCount", count(Table1[key]),"Status", FIRSTNONBLANK(Table1[Status],1=1),"Key", FIRSTNONBLANK(Table1[key],1=1))

With this calculation, I am ending up with a table like the following. The last column is calculated after table creation based on DoneCount / CountCount. Note how item E, with an ID of 5, did not make it over

BigLevelKey	DoneCount	CountCount	Status	zItemPercentColumn
1	1	1	In Progress	100%
4	0	1	Not started	0%
6	1	4	In Progress	25%
11	3	4	In Progress	75%

The expected output is as follows

BigLevelKey	DoneCount	CountCount	Status	zItemPercentColumn
1	1	1	In Progress	100%
4	0	1	Not Started	0%
5	0	0	Not Started	0%
6	1	4	In Progress	25%
11	3	4	In Progress	75%

I need to fix my calculated table so that it brings over item E, which is also in the Big category, even though it has no child items to count and collect info on if they are done or not.

Let me know if you have any questions about my problem, any and all advice is greatly appreciated.

[Anonymous · Accepted solution]

Hi @jarwest ,

Based on the information you have provided, you can follow these steps:

1.Add new table

TableSummarized =

SUMMARIZE (

    FILTER ( 'Table', 'Table'[Category] = "Medium" || 'Table'[Category] = "Small" ||'Table'[Category]="Big"),

    'Table'[BigLevelKey],

    "DoneCount", SUM ( 'Table'[isDone] ),

    "CountCount", COUNT ( 'Table'[key] )-1,

    "Status", FIRSTNONBLANK ( 'Table'[Status], 1 = 1 ),

    "Key", FIRSTNONBLANK ( 'Table'[key], 1 = 1 )

)

2.Add new column

PercentColumn =

VAR _1 = 'TableSummarized'[DoneCount] / 'TableSummarized'[CountCount]

RETURN

    IF ( _1 >= 0, _1, 0 )

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

^{Best Regards,}

^{Yifan Wang}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[ryan_mayu]

@jarwest 

is this what you want?

Table 2 = 
var tbl=ADDCOLUMNS('Table',"percent",if(maxx(FILTER('Table','Table'[BigLevelKey]=EARLIER('Table'[BigLevelKey])&&'Table'[Status]="Done"),'Table'[Status])="",0,1))
return SUMMARIZE(FILTER(tbl,'Table'[Category]="Big"),'Table'[BigLevelKey],'Table'[Name],'Table'[Status],[percent])

[jarwest]

Hey Ryan_mayu,

Not quite. It looks like this is hard coding in percent as a binary value, which isn't exactly what I need. [percent] needs to be the percentage of items associated with the big item in that row that are marked as done under status. I apologize for the poor examples, I've got some big items that have 10-20 medium and small items associated with it, If I have 15 items associated with a big item and 5 are marked done, I would need percent to show %33.333.

Let me know if you have any follow up questions. 

[ryan_mayu]

the percent column I created can be changed to percentage type. Then you will see 100% and 0%.

if the solution is still not correct, pls update the sample data and the expected output.

[jarwest]

Added additional data. If I can get the total number of items for each big item, and the number of those items that have a status of 'Done', I can make the calculation for percentage on my end with a calculated column just saying 'DoneCount/TotalCount' or something to that effect. Let me know if you need further information from me.

[ryan_mayu]

@jarwest 

i saw your new sample data 

BigLevelKey	DoneCount	CountCount	Status	zItemPercentColumn
1	1	1	In Progress	100%
4	0	1	Not Started	0%
5	0	0	Not Started	0%
6	1	4	In Progress	25%
11	3	4	In Progress	75%

how to get the countcout? why both 6 and 11 are 4?

[jarwest]

Hey, pointing back to the original data set here, there are 5 items labelled 'Big'. The column 'BigLevelKey' is used to determine what Big item the Small and Medium items belong to. 

Big item 1 has a count of 1 because item 2 has a BigLevelKey value of 1. 

Big item 4 has a count of 1 because item 3 has a BigLevelKey value of 4.

Big item 5 has a count of 0 because no items have a BigLevelKey value of 5.

Big item 6 has a count of 4 because items 7, 8, 9, and 10 have a BigLevelKey value of 6.

Big item 11 has a count of 4 because items 11, 12, 13, and 14 has a BigLevelKey value of 11.

Additionally, donecount is counting the number of items under a big item that are done.

For example, Big item 6 has 4 items under it, as we mentioned, but only item 7 has a status of 'Done'. Therefore, Big item 6 has a donecount of 1.

I hope this helps