Forum Discussion
Line Chart with Multi-Output Data
Hello.
I'm looking to create a line chart with opportunity close date on the x-axis and win % on the y-axis. I am then looking to have 3 separate lines showing the win % by product over time. The complexity is that multiple products can be associated with a single opportunity and are listed in a single column of the data source. I want the lines in the chart to track the win % of a given product regardless of the other products associated with that opportunity - one line for product A, one line for B, and one line for C (so some opportunities will be counted more than once and contribute to the lines of more than one product). A hyper-simplified version of the data is shown below. Is this possible? Perhaps with some sort of nested if/ contains text calculation?
| Opportunity ID | Date Closed | Won? | Products |
| 1 | 1/2/2018 | Won | A |
| 2 | 3/16/2018 | Won | B, C |
| 3 | 4/30/2019 | Won | C, A |
| 4 | 10/27/2019 | Lost | B, C, A |
| 5 | 2/2/2020 | Won | C |
| 6 | 2/7/2020 | Lost | C, B |
| 7 | 4/15/2020 | Lost | A, B |
| 8 | 6/7/2020 | Won | A |
| 9 | 12/13/2020 | Won | A, B, C |
| 10 | 1/5/2021 | Lost | A |
Any help would be greatly appreciated.
Hi Anonymous ,
First, you need to transform your table to below:
Please refer to the M query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XZC7DsIwDEV/JcocKXHSB0yojwUJCTaGqgOiHZBQU7XpwN/juA0tXZLYx/faTlXxa9/bwU3dy33YueSClw/XsuJtx7bB6G67E163wTbT0428FhUHTIDUUis4zCV4ZoQ0voyE5J/lghWEDQaRNMrj4w8Xgs3qyBsjTAO/2NEt+lATY6ypuVarA6GEUBrQIkZlTjil5hDveBa4nzdZ5du1/CygJZgdRGlYDRT9CrnDxp3X9Rc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}}), #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Opportunity ID", Int64.Type}, {"Date Closed", type date}, {"Won?", type text}, {"Products", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type1", "Products", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Products.1", "Products.2", "Products.3"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Products.1", type text}, {"Products.2", type text}, {"Products.3", type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type2", {"Opportunity ID", "Date Closed", "Won?"}, "Attribute", "Value"), #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns"," ","",Replacer.ReplaceText,{"Value"}) in #"Replaced Value"If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
2 Replies
Anonymous , I would suggest to split prodcut in power query in rows and then use it
https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/
Hi Anonymous ,
First, you need to transform your table to below:
Please refer to the M query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XZC7DsIwDEV/JcocKXHSB0yojwUJCTaGqgOiHZBQU7XpwN/juA0tXZLYx/faTlXxa9/bwU3dy33YueSClw/XsuJtx7bB6G67E163wTbT0428FhUHTIDUUis4zCV4ZoQ0voyE5J/lghWEDQaRNMrj4w8Xgs3qyBsjTAO/2NEt+lATY6ypuVarA6GEUBrQIkZlTjil5hDveBa4nzdZ5du1/CygJZgdRGlYDRT9CrnDxp3X9Rc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}}), #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Opportunity ID", Int64.Type}, {"Date Closed", type date}, {"Won?", type text}, {"Products", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type1", "Products", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Products.1", "Products.2", "Products.3"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Products.1", type text}, {"Products.2", type text}, {"Products.3", type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type2", {"Opportunity ID", "Date Closed", "Won?"}, "Attribute", "Value"), #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns"," ","",Replacer.ReplaceText,{"Value"}) in #"Replaced Value"If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
