Calculate percentage for donut chart

Hi, I feel confuse on how does the donut chart percentage being calculated.

I have a situation that I need to display percentage of task completion for each of the project in the donut chart.

For example, MLOps Project has 2 Linked Issue tasks (IFMY-226) created and 1 task is closed (completed). So, the percentage of task completion for MLOps Project is 50% (total closed tasks/ total tasks created). How can I display the percentage of task completion for each of the project in the donut chart?

Here is my current data

let
    Source = Json.Document(Web.Contents("https://XXX/XXX/XXX")),
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Expanded issues" = Table.ExpandListColumn(#"Converted to Table", "issues"),
    #"Expanded issues1" = Table.ExpandRecordColumn(#"Expanded issues", "issues", {"expand", "id", "self", "key", "fields"}, {"issues.expand", "issues.id", "issues.self", "issues.key", "issues.fields"}),
    #"Expanded issues.fields" = Table.ExpandRecordColumn(#"Expanded issues1", "issues.fields", {"summary", "issuetype", "components", "created", "fixVersions", "priority", "customfield_34216", "labels", "customfield_11900", "customfield_35401", "customfield_11901", "duedate", "resolutiondate", "assignee", "updated", "status"}, {"issues.fields.summary", "issues.fields.issuetype", "issues.fields.components", "issues.fields.created", "issues.fields.fixVersions", "issues.fields.priority", "issues.fields.customfield_34216", "issues.fields.labels", "issues.fields.customfield_11900", "issues.fields.customfield_35401", "issues.fields.customfield_11901", "issues.fields.duedate", "issues.fields.resolutiondate", "issues.fields.assignee", "issues.fields.updated", "issues.fields.status"}),
    #"Expanded issues.fields.customfield_35401" = Table.ExpandListColumn(#"Expanded issues.fields", "issues.fields.customfield_35401"),
    #"Expanded issues.fields.customfield_1" = Table.ExpandRecordColumn(#"Expanded issues.fields.customfield_35401", "issues.fields.customfield_35401", {"displayName"}, {"issues.fields.customfield_35401.displayName"}),
    Sponsor = Table.RenameColumns(#"Expanded issues.fields.customfield_1",{{"issues.fields.customfield_35401.displayName", "issues.fields.customfield_35401"}}),
    #"Expanded issues.fields.assignee" = Table.ExpandRecordColumn(Sponsor, "issues.fields.assignee", {"displayName"}, {"issues.fields.assignee.displayName"}),
    Assignee = Table.RenameColumns(#"Expanded issues.fields.assignee",{{"issues.fields.assignee.displayName", "issues.fields.assignee"}}),
    #"Expanded issues.fields.fixVersions" = Table.ExpandListColumn(Assignee, "issues.fields.fixVersions"),
    #"Expanded issues.fields.fixVersions1" = Table.ExpandRecordColumn(#"Expanded issues.fields.fixVersions", "issues.fields.fixVersions", {"name"}, {"issues.fields.fixVersions.name"}),
    Version = Table.RenameColumns(#"Expanded issues.fields.fixVersions1",{{"issues.fields.fixVersions.name", "issues.fields.fixVersions"}}),
    #"Expanded issues.fields.components" = Table.ExpandListColumn(Version, "issues.fields.components"),
    #"Expanded issues.fields.components1" = Table.ExpandRecordColumn(#"Expanded issues.fields.components", "issues.fields.components", {"name"}, {"issues.fields.components.name"}),
    Components = Table.RenameColumns(#"Expanded issues.fields.components1",{{"issues.fields.components.name", "issues.fields.components"}}),
    #"Expanded issues.fields.labels" = Table.ExpandListColumn(Components, "issues.fields.labels"),
    #"Expanded issues.fields.issuetype" = Table.ExpandRecordColumn(#"Expanded issues.fields.labels", "issues.fields.issuetype", {"self", "id", "description", "iconUrl", "name", "subtask", "avatarId"}, {"issues.fields.issuetype.self", "issues.fields.issuetype.id", "issues.fields.issuetype.description", "issues.fields.issuetype.iconUrl", "issues.fields.issuetype.name", "issues.fields.issuetype.subtask", "issues.fields.issuetype.avatarId"}),
    #"Expanded issues.fields.priority" = Table.ExpandRecordColumn(#"Expanded issues.fields.issuetype", "issues.fields.priority", {"self", "iconUrl", "name", "id"}, {"issues.fields.priority.self", "issues.fields.priority.iconUrl", "issues.fields.priority.name", "issues.fields.priority.id"}),
    #"Expanded issues.fields.status" = Table.ExpandRecordColumn(#"Expanded issues.fields.priority", "issues.fields.status", {"self", "description", "iconUrl", "name", "id", "statusCategory"}, {"issues.fields.status.self", "issues.fields.status.description", "issues.fields.status.iconUrl", "issues.fields.status.name", "issues.fields.status.id", "issues.fields.status.statusCategory"}),
    #"Expanded issues.fields.status.statusCategory" = Table.ExpandRecordColumn(#"Expanded issues.fields.status", "issues.fields.status.statusCategory", {"self", "id", "key", "colorName", "name"}, {"issues.fields.status.statusCategory.self", "issues.fields.status.statusCategory.id", "issues.fields.status.statusCategory.key", "issues.fields.status.statusCategory.colorName", "issues.fields.status.statusCategory.name"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded issues.fields.status.statusCategory",{{"expand", type text}, {"startAt", Int64.Type}, {"maxResults", Int64.Type}, {"total", Int64.Type}, {"issues.expand", type text}, {"issues.id", Int64.Type}, {"issues.self", type text}, {"issues.key", type text}, {"issues.fields.summary", type text}, {"issues.fields.issuetype.self", type text}, {"issues.fields.issuetype.id", Int64.Type}, {"issues.fields.issuetype.description", type text}, {"issues.fields.issuetype.iconUrl", type text}, {"issues.fields.issuetype.name", type text}, {"issues.fields.issuetype.subtask", type logical}, {"issues.fields.issuetype.avatarId", Int64.Type}, {"issues.fields.components", type any}, {"issues.fields.created", type datetimezone}, {"issues.fields.fixVersions", type any}, {"issues.fields.priority.self", type text}, {"issues.fields.priority.iconUrl", type text}, {"issues.fields.priority.name", type text}, {"issues.fields.priority.id", Int64.Type}, {"issues.fields.customfield_34216", Int64.Type}, {"issues.fields.labels", type any}, {"issues.fields.customfield_11900", type text}, {"issues.fields.customfield_35401", type any}, {"issues.fields.customfield_11901", type text}, {"issues.fields.duedate", type date}, {"issues.fields.resolutiondate", type datetimezone}, {"issues.fields.assignee", type any}, {"issues.fields.updated", type datetimezone}, {"issues.fields.status.self", type text}, {"issues.fields.status.description", type text}, {"issues.fields.status.iconUrl", type text}, {"issues.fields.status.name", type text}, {"issues.fields.status.id", Int64.Type}, {"issues.fields.status.statusCategory.self", type text}, {"issues.fields.status.statusCategory.id", Int64.Type}, {"issues.fields.status.statusCategory.key", type text}, {"issues.fields.status.statusCategory.colorName", type text}, {"issues.fields.status.statusCategory.name", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"issues.key"}, {{"Project Name", each List.Max([issues.fields.customfield_11901]), type nullable text}, {"Summary", each List.Max([issues.fields.summary]), type nullable text}, {"Priority", each List.Max([issues.fields.priority.name]), type nullable text}, {"Sponsor", each List.Max([issues.fields.customfield_35401]), type nullable list}, {"Assignee", each List.Max([issues.fields.assignee]), type nullable text}, {"Components", each Text.Combine([issues.fields.components], ";"), type nullable text}, {"Labels", each List.Max([issues.fields.labels]), type nullable text}, {"User Business", each List.Max([issues.fields.customfield_34216]), type nullable number}, {"Status", each List.Max([issues.fields.status.name]), type nullable text}, {"Issue Type", each List.Max([issues.fields.issuetype.name]), type nullable text}, {"Version", each List.Max([issues.fields.fixVersions]), type nullable text}, {"Created Date", each List.Max([issues.fields.created]), type nullable datetimezone}, {"Due Date", each List.Max([issues.fields.duedate]), type nullable date}, {"Resolution Date", each List.Max([issues.fields.resolutiondate]), type nullable datetimezone}, {"Linked Issue", each List.Max([issues.fields.customfield_11900]), type nullable text}}),
    Type = Table.TransformColumnTypes(#"Grouped Rows",{{"Issue Type", type text}, {"issues.key", type text}, {"Project Name", type text}, {"Linked Issue", type text}}),
    Columns = Table.SelectColumns(Type,{"issues.key", "Project Name"}),
    Rows = Table.SelectRows(Columns, each ([Project Name] <> "")),
    Merged = Table.NestedJoin(Type, {"Linked Issue"}, Rows, {"issues.key"}, "Move Columns", JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Merged, "Move Columns", {"Project Name"}, {"Project Name Full"}),
    ColumnProjectNameNew = Table.AddColumn(Expand, "Project Name New", each if [Project Name] = null
        then [Project Name Full]
        else [Project Name])
in
    #"ColumnProjectNameNew"

Need Help

Show and Tell

Tips and Tricks

Anonymous
2 years ago
Hi Anonymous

Thanks for your quick reply, if you want to display '0%', you need to change the donut chart to table visual, because the donut chart cannot display the value '0' , and change the measure to the following.

MEASURE = VAR a = CALCULATE ( DISTINCTCOUNT ( 'Table'[Linked Issue] ), 'Table'[Status] = "Closed" ) VAR b = CALCULATE ( COUNTA ( 'Table'[Linked Issue] ), ALLSELECTED ( 'Table' ), 'Table'[Project Name] IN VALUES ( 'Table'[Project Name] ) ) RETURN IF ( a > 0, DIVIDE ( a, b ), 0 )

Then it can display.

Best Regards!

Yolo Zhu

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

Forum Discussion

Calculate percentage for donut chart

5 Replies

Related content

Donut Chart - dynamic percentage

Calculate Percentage

Calculating Percentages

Percentage Donuts

Calculate percentage