Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Beginner123
Regular Visitor

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?

Beginner123_0-1718115713766.png

 

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"

 

 

1 ACCEPTED SOLUTION

Hi @Beginner123 

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.

vxinruzhumsft_0-1718169696149.png

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.

View solution in original post

5 REPLIES 5
Beginner123
Regular Visitor

Hi @ManuelBolz not sure if you have any idea on this? Any advice? Thanks in advance

Hi @Beginner123  

You can refer to the following solution.

The query you have offered cannot use, so i create a sample data

Sample data 

vxinruzhumsft_0-1718157095585.png

You can create a measure

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
    DIVIDE ( a, b )

Then change the measure format to percentage and keep two decimal numbers

vxinruzhumsft_1-1718157196871.png

 

Then put the measure to the donut chart,and change the label contents, set it only display value.

vxinruzhumsft_2-1718157263790.png

 

Output

vxinruzhumsft_3-1718157272104.png

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.

 

 

 

Hi @v-xinruzhu-msft, thank you so much for the advice. I have tried but the Measure (% Completion) will only show the project with closed task as below.

 

Beginner123_0-1718161756919.png

 

I want to have the project without closed task calculated as well.

Beginner123_1-1718161818517.png

 

In the end, i would like to have:

MLOps Project = 50%

LLMOps = 0%

MLOps for NPU = 0%

LLM-RAG = 0%

Wifi Sensing Activity Recognition = 0%

Hi @Beginner123 

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.

vxinruzhumsft_0-1718169696149.png

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.

Hi @v-xinruzhu-msft thank you so much for the help. It works perfectly now! 🙂

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors