Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
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.
Solved! Go to 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.
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.
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])
Proud to be a Super User!
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.
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.
Proud to be a Super User!
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.
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?
Proud to be a Super User!
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |