Making a new table from another table with calculation

[briankwlo]

Hello,

Sample Data:

courseid	userid	fullname	completion_state	division
1	1	Reading course	finished	AAA
1	2	Reading course	Not finish	AAA
1	3	Reading course	Not finish	BBB
1	4	Reading course	Not finish	CCC
1	5	Reading course	Not finish	DDD
2	1	Practical	Not finish	AAA
2	2	Practical	Not finish	AAA
2	3	Practical	finished	BBB
2	4	Practical	Not finish	CCC
2	5	Practical	Not finish	DDD

with filter courseid = 1:

division	total	finished	percentage
AAA	2	1	50%
BBB	1	0	0%
CCC	1	0	0%
DDD	1	0	0%

with filter courseid = 2:

division	total	finished	percentage
AAA	2	0	0%
BBB	1	1	100%
CCC	1	0	0%
DDD	1	0	0%

Would anyone help about this situation and suggest me how to create a new table for this?

Thanks!

[Anonymous · Accepted solution]

Hi @briankwlo 

You can create the below measures

Finished = CALCULATE(COUNT(Course[division]),Course[completion_state] = "finished")+0

percentage = DIVIDE([Finished],[Total],0)

Total = CALCULATE(COUNT(Course[division]),ALLEXCEPT(Course,Course[courseid],Course[division]))

This is the output of the measures in the table visual

If this post helps, Accept it as a solution

[briankwlo]

It's work! 

Thank you esha_shah2002! 

Have a nice day!