Non-numerical measures/status update (percentages) on completion of a form

[Airborne82]

Hello all! 

I'm currently having trouble finding ways to measure the completion and putting a percentage vaule of completion per each indivudual row. I need to have the abiliity to show completion by:

(1) Total percentage form completion by Region as the parent container

(2) Total percentage form completion by State

(3) Percentage form completion by Individual row

Statement:

My current project is utilizing sharepoint as our data source where our cilents are allowing facility managers to fillout their form updates for their assigned facilitiy. We are currently monitoring over 2000+ facilities and our leadership wants snapshots of progress on completion of the form. Each Row is identified as a seperate facility, and when viewing the information the calulation would be at the end of each row of information as a percentage value.

For example purpose:

(Each row stands as an independent facility to track completion of the form to be filled out)

Region - is our Parent container that will provide complete progess of all states that are within it

States - Is our Child container that will provide complete progress of all faciliites within its State

Certified Review - Represents when the Facilitiy manager certifies completion of the form (which equals 100% complete)

Notes: Per example provided above, there are 2 completed forms (rows) which would equal 100% compliance; and the other rows are incomplete and need further input.

(e.g = Since their are 11 coulmns each cell would equal roughly (9.091 % )

Row 2 = 90.9% completed

Row 3 = 72.72% completed

Row 4 = 100% completed

ect. 

Problem:

How do I put a percentage measure of completion to non-numerical row inputs in the effort to create an executive view dashboard of progress to leadership?

It has to have the abiliity to be sliced by Region as the Parent then drilled down to state, and then each individual row/facilitiy within the state.

Thank you again for any assistance!

[v-kkf-msft · Accepted solution]

Hi @Airborne82 ,

Please add a custom column in Power Query Editor.

= List.Count( List.RemoveItems( Record.FieldValues(_), {"",null})) / List.Count(Table.ColumnNames(#"Changed Type"))

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Airborne82]

Thank you, I was able to eventually get it to work

!

Im very new to powerBI, and it took sometime to figure out the changing values of your code. (e.g. Changed Type = my current applied steps).

I do have one more question, if you have the time. Could you give some description or semi-breakdown what your code is doing?

Thank you again for your help!

[v-kkf-msft]

Hi @Airborne82 ,

1. Table.ColumnNames(#"Changed Type") Returns all the column names in the table after the "Changed Type" step.

2. Then use List.Count to calculate the number of returned column names:

List.Count(Table.ColumnNames(#"Changed Type")) 

3. Record.FieldValues(_) converts the data in the current row into a list.

For example, the first row:

4. Then remove the null values from this list and count the number of items remaining in the list:

List.Count( List.RemoveItems( Record.FieldValues(_), {"",null}))

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.