Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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"
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |