Forum Discussion
Measure help
I have a problem I am struggling to solve. I have a table with items sold in 4 different columns and the total sales of the item sold in that column in 4 different columns. (See Below).
I need to calculate the total sales of Each item (Bikes, Trains, etc.) regardless of the column its in.
For example,"Bikes" is reference in Item column 1, 2, & in column 3 twice, thus would need to sum the values in Value column 1 (Row 2, "556"), Value column 2(row 3, "3815"), and Value Column 3(row 4 "2546" & row 6 "644") to See "Bikes" = 7561.
This would need to be done for Each of the items sold.
| Item 1 | Item 2 | Item 3 | Item 4 | Value 1 | Value 2 | Value 3 | Value 4 |
| Bikes | Trains | 556 | 1600 | ||||
| Trains | Bikes | Wheels | 1986 | 3815 | 2065 | ||
| Wheels | Spokes | Bikes | Trains | 2648 | 46 | 2546 | 16000 |
| Spokes | Wheels | 11 | 298 | ||||
| Diamonds | Rivets | Bikes | 256 | 645 | 644 | ||
| Rivets | Bikes | Traoms | Wheels | 6448 | 23445 | 2205 | 1785 |
Thank you for any help you can provide.
I think you will be better off transforming our table into 2 columns (items and Values) using Power Query
File attached as well
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY7BDsIgEER/peHcA9BdpFfjF6iJB8KhiSQSbWnU+P2yKKtND+xsyLyZdU5s4zU8RCuO9yFOtDTfh2jyVEZK/vSt+/kqeLqEcKug6i1RnVWYRUuDDLLvMKcPuarWBmwWoAiNUPtl4RnjoNqpyN7bxZm7OIxpOpNtH1/h+d/XlHhKN4BlAnMrcz4ujYvabKcu3UGhtZYkamNReP8G", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Item 1" = _t, #"Item 2" = _t, #"Item 3" = _t, #"Item 4" = _t, #"Value 1" = _t, #"Value 2" = _t, #"Value 3" = _t, #"Value 4" = _t]), step = Table.TransformColumnTypes(Source,{{"Item 1", type text}, {"Item 2", type text}, {"Item 3", type text}, {"Item 4", type text}, {"Value 1", Int64.Type}, {"Value 2", Int64.Type}, {"Value 3", Int64.Type}, {"Value 4", Int64.Type}}), G1 = Table.FromColumns({step[Item 1],step[Value 1]},{"Items","Value"}), G2 = Table.FromColumns({step[Item 2],step[Value 2]},{"Items","Value"}), G3 = Table.FromColumns({step[Item 3],step[Value 3]},{"Items","Value"}), G4 = Table.FromColumns({step[Item 4],step[Value 4]},{"Items","Value"}), Final=Table.Combine({G1,G2,G3,G4}), #"Filtered Rows" = Table.SelectRows(Final, each ([Items] <> " ") and ([Value] <> null)) in #"Filtered Rows"
1 Reply
I think you will be better off transforming our table into 2 columns (items and Values) using Power Query
File attached as well
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY7BDsIgEER/peHcA9BdpFfjF6iJB8KhiSQSbWnU+P2yKKtND+xsyLyZdU5s4zU8RCuO9yFOtDTfh2jyVEZK/vSt+/kqeLqEcKug6i1RnVWYRUuDDLLvMKcPuarWBmwWoAiNUPtl4RnjoNqpyN7bxZm7OIxpOpNtH1/h+d/XlHhKN4BlAnMrcz4ujYvabKcu3UGhtZYkamNReP8G", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Item 1" = _t, #"Item 2" = _t, #"Item 3" = _t, #"Item 4" = _t, #"Value 1" = _t, #"Value 2" = _t, #"Value 3" = _t, #"Value 4" = _t]), step = Table.TransformColumnTypes(Source,{{"Item 1", type text}, {"Item 2", type text}, {"Item 3", type text}, {"Item 4", type text}, {"Value 1", Int64.Type}, {"Value 2", Int64.Type}, {"Value 3", Int64.Type}, {"Value 4", Int64.Type}}), G1 = Table.FromColumns({step[Item 1],step[Value 1]},{"Items","Value"}), G2 = Table.FromColumns({step[Item 2],step[Value 2]},{"Items","Value"}), G3 = Table.FromColumns({step[Item 3],step[Value 3]},{"Items","Value"}), G4 = Table.FromColumns({step[Item 4],step[Value 4]},{"Items","Value"}), Final=Table.Combine({G1,G2,G3,G4}), #"Filtered Rows" = Table.SelectRows(Final, each ([Items] <> " ") and ([Value] <> null)) in #"Filtered Rows"