Calculating Percentage of Completed Courses

[BParent]

Hey all,

I'm at a bit of a loss on how to tackle the following problem.

I have a dataset that is basically

Name	Course
Bryan	1.A
Bryan	1.B
Bryan	1.C
David	1.A

I'd like to add a third column there that says what percent complete Bryan and David are at through the course of study (Complete being defined as all three courses, 1.A, 1.B, 1.C).

So it would eventually look like this:

Name	Course	Completion % of All Courses
Bryan	1.A	100%
Bryan	1.B	100%
Bryan	1.C	100%
David	1.A	33%

I might just be tired, but I did try searching around and didn't find this scenerio. 

Appreciate any help.

[v-lgarikapat · Accepted solution]

Hi  @BParent ,

Thank you for reaching out to the Microsoft Fabric Community Forum.

 @SundarRaj  @Omid_Motamedise  I appreciate your prompt responses and for sharing the Power Query-based solutions.

 @BParent In addition to that, this scenario can also be achieved using DAX. I’ve uploaded a sample PBIX file for your reference. Please feel free to review it at your convenience, and I’d be grateful for any feedback you may have.

If this post helped resolve your issue, please consider the Accepted Solution. This not only acknowledges the support provided but also helps other community members find relevant solutions more easily.

We appreciate your engagement and thank you for being an active part of the community.

Best regards,
LakshmiNarayana.

[v-lgarikapat]

Hi @BParent ,

I wanted to follow up and confirm whether you’ve had the opportunity to review the information we provided. Should you have any questions or require further clarification, please don't hesitate to reach out.

Best Regards,

Lakshmi Naraayna

[SundarRaj]

Hi @BParent , another PQ solution. Thanks!

Here's the code:
let
Source = #table(
{"Name", "Course"},
{
{"Bryan", "1.A"},
{"Bryan", "1.B"},
{"Bryan", "1.C"},
{"David", "1.A"}
}
),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Course", type text}}),
CoursesAv = List.Count ( List.Distinct ( #"Changed Type" [Course] ) ),
Group = Table.Group( #"Changed Type", { "Name" }, { { "Course" , each _[Course] } , { "Course Completion" , each Table.RowCount( _ ) , Int64.Type } } ),
Percentage = Table.TransformColumns ( Group , { "Course Completion" , each _ / CoursesAv , Percentage.Type } ),
Expand = Table.ExpandListColumn(Percentage, "Course")
in
Expand

[Omid_Motamedise]

Copy and paste the following code into the advanced editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciqqTMxT0lEy1HFUitVB5juh8vWcwXyXxLLMFDAfqD4WAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Course = _t]),
    #"Added Custom" = Table.AddColumn(Source, "% of complete", each Table.RowCount(Table.SelectRows(Source,(x)=> x[Name]=_[Name]))/3)
in
    #"Added Custom"