How to get a subtotal and a total

[Chris_cons]

Hello,

i am new to power bi, and i am struggling with something i believed would be basic.

i have a datatable with this structure (simplified):

Project Epic US US done (1 if done)

P1	E1	US1	1
P1	E1	US2	0
P1	E1	US3	0
P1	E2	US4	1
P1	E2	US5	1
P2	E3	US6	1
P2	E3	US7	1

"US done" tells me if the user story is done (1) or not (!= 1). An epic is done if all associated user stories are done.

I am trying to get a visual of the number of Epic done per project, for example a table (based on the previous example):

Project Epic done Total number of epic

P1	1	2
P2	1	1

I calculated the total number of US: total_us = COUNT(US)

and the number of US that are done total_done_us = CALCULATE( COUNT(US) , US Done = 1 )

And then i calculate the difference between total_done_us and total_us: Epic done = if(total_us - total_done_us =0, 1,0)

but it does not takes the epics into account, only the total of user stories.

I tried using a table or a matrix visual, but i do not success in having only the project name and the total of epic done per project and the total of epic per project.

[Chris_cons]

Thanks Bolfri !

it works, i still have some work to understand some of the magical line:

Total Epic - Done = COUNTAX(FILTER(GROUPBY(SampleData,SampleData[Epic]),[Total US] = [Total US - Done]),1)

---

[bolfri]

GROUPBY(SampleData,SampleData[Epic]) is same as DISTINCT(SampleData[Epic])

For each unique Epic check if Total US = Total US - Done. If yes, mark it as 1. 🙂 COUNTAX SUMs Epic by this 1. 

[bolfri · Accepted solution]

Total US = DISTINCTCOUNT(SampleData[US])
Total US - Done = CALCULATE([Total US],SampleData[US done] = 1)
Total Epic = DISTINCTCOUNT(SampleData[Epic])
Total Epic - Done = COUNTAX(FILTER(GROUPBY(SampleData,SampleData[Epic]),[Total US] = [Total US - Done]),1)