Forum Discussion
Create a Table with All Possible Combos from a Single Column and Data for Each Combo
Hi everyone,
Posting here for the first time. This forum has helped a lot over the past few months of learning Power BI. I could use some help with something that I haven't seen in my googling.
Updated (7/25/22) the tables for clarification:
Let's say I have a data table that looks like this:
| SuperGroup | Group | Value |
| A | 1 | 5 |
| A | 1 | 5 |
| A | 2 | 7 |
| A | 2 | 7 |
| A | 3 | 2 |
| A | 3 | 1 |
| A | 4 | 4 |
| A | 4 | 6 |
| B | 5 | 3 |
| B | 5 | 3 |
| C | 6 | 3 |
| C | 6 | 2 |
| C | 7 | 9 |
| C | 7 | 8 |
My goal is to be able to tell which combination of groups would provide the lowest %CV within each SuperGroup. So ultimately, I'd like a table (table visual) that looks like this:
| SuperGroup | Combo | Average | CV |
| A | 1 | 5 | 0% |
| A | 2 | 7 | 0% |
| A | 3 | 1.5 | 47% |
| A | 4 | 5 | 28% |
| A | 1,2 | 6 | 19% |
| A | 2,3 | 4.25 | 75% |
| A | 3,4 | 3.25 | 68% |
| A | 1,3 | 3.25 | 63% |
| A | 1,4 | 5 | 16% |
| A | 2,4 | 6 | 24% |
| A | 1,2,3 | 4.5 | 56% |
| A | 1,2,4 | 5.67 | 21% |
| A | 2,3,4 | 4.5 | 58% |
| A | 1,3,4 | 3.83 | 51% |
| A | 1,2,3,4 | 4.625 | 48% |
| B | 5 | 3 | 0% |
| C | 6 | 2.5 | 28% |
| C | 7 | 8.5 | 8% |
| C | 6,7 | 5.5 | 64% |
Another thing to note is that I will only ever be looking at 1-4 groups at a time, so the number of combos won't be endless, but the Groups will change over time (e.g. could be looking at combos with Groups 1201, 1202, 1203, 1204).
Is this possible?
Thanks in advance!
See attached for a solution with SuperGroups
13 Replies
You can, but they come at a cost. You may run into Formula Firewall issues if not careful. Note that you can also inline them into your main query, that should keep everything inside the same partition.
Interesting problem. Aren't you missing the four "groups of one" ?
The Group average for 1,2 is 5.75, not 6. Please correct your sample data.
Please explain what %CV means and how it is calculated.
Here is the first step - create all possible combinations:
Table:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTJVitWBsEzALCMgyxyNZQxkGcFZhmCWCVwHiGWmFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Group = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group", type text}, {"Value", Int64.Type}}) in #"Changed Type"Combo:
let Source = Table[Group], #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Removed Duplicates" = Table.Distinct(#"Converted to Table"), #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Custom", each #"Removed Duplicates"), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1"}, {"Column2"}), #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each #"Removed Duplicates"), #"Expanded Custom1" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"Column1"}, {"Column3"}), #"Added Custom2" = Table.AddColumn(#"Expanded Custom1", "Custom", each #"Removed Duplicates"), #"Expanded Custom2" = Table.ExpandTableColumn(#"Added Custom2", "Custom", {"Column1"}, {"Column4"}), #"Added Custom3" = Table.AddColumn(#"Expanded Custom2", "Custom", each List.Sort(List.Distinct({[Column1],[Column2],[Column3],[Column4]}))), #"Extracted Values" = Table.TransformColumns(#"Added Custom3", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}), #"Removed Duplicates1" = Table.Distinct(#"Extracted Values", {"Custom"}) in #"Removed Duplicates1"And here is the second part - calculating the averages
Hi Anonymous
what is the %CV?
