Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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
Hi @steveray,
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
Hi @v-shex-msft
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
Hi @steveray,
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
Hi @steveray,
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
@steveray , can you share the expected output?
Can you share sample data and sample output in table format?
User | Count |
---|---|
94 | |
83 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
62 |