Displaying multiple Yes/No field in a % complete visual

[Anonymous]

Hi All, 

This is my first post so go easy on me!

I am creating a report dashboard that is capturing the progress of a document approval process (the data is being captured in a Yes/No column on a sharepoint list). At the completion of each step the column in the sharepoint list is changed to Yes.

How do i convert the Yes/No data to allow me to display these results as a percentage complete in a visual?

Any assistance would be grately appreciated.

Thanks

[Anonymous]

@amitchandak 

Document Request #	1st Draft Complete	2nd Draft Complete	Final Draft Approved	Uploaded	Task Completed
1	Yes	Yes	Yes	Yes	Yes
2	Yes	Yes	Yes	Yes	No
3	Yes	Yes	Yes	No	No
4	Yes	Yes	No	No	No
5	Yes	No	No	No	No
6	No	No	No	No	No

For Example in the report when 'Document Request #1' is selected a visual such as a 'gauge' will be displayed showing the percentage complete (e.g 100% complete).

I would like to be able to give the requestor a visual on the status of their request.

Hope that make sense

Thanks

[Anonymous]

Hi @Anonymous,

I'd like to suggest you do unpivot column on your fields to attribute and values, then you can simply create a DAX formula based on the current attribute to calculate them to get the percent of values.

Unpivot columns (Power Query) (microsoft.com)
Regards,
Xiaoxin Sheng

[Anonymous]

Hi @Anonymous 

I'm not not sure if i am missing something but when i unpivot the columns all the data is replaced by 'Error'?

I am not an expert in power BI or DAX formulas, maybe i am trying to bit off something above my knowledge?

Thanks

Steve

[Anonymous]

Hi @Anonymous,

What error message that your query table shows? (you can click on these 'error' to see detailed information)
BTW, I tested with your dummy data and unpivot column works well, you can check them if helps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUYpMLcZJxupEKxnhUeOXD1ZijEsSrsIEhxxcgSl2Kbi8GVYZiHQsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Document Request #" = _t, #"1st Draft Complete" = _t, #"2nd Draft Complete" = _t, #"Final Draft Approved" = _t, Uploaded = _t, #"Task Completed" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Document Request #", Int64.Type}, {"1st Draft Complete", type text}, {"2nd Draft Complete", type text}, {"Final Draft Approved", type text}, {"Uploaded", type text}, {"Task Completed", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Document Request #"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @Anonymous 

Here is the error meesage

Thanks

[Anonymous]

Hi @Anonymous,

Perhaps you can take a look at the following link that mention the same error messages if it helps:

Re: project online custom fields - Microsoft Power BI Community
Regards,

Xiaoxin Sheng

[amitchandak]

@Anonymous , can you share the expected output?

Can you share sample data and sample output in table format?