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.
Hi,
I have a set of data as follow.
LocalString | Units | Value | Sum |
America | 25 | 10 | 250 |
Australia | 3 | 50 | 150 |
America | 89 | 20 | 1780 |
And so on.
Before, I would group and take the average value. But the introduction of Units means I need a variant of Sumproduct() formula.
Is there a way, using PowerQuery only, to perform the following operation ?
For each America = (250+1780)/(25+89)
Thanks
Solved! Go to Solution.
The code below should do the trick. Just adapt your List.Average to List.Sum([Sum])/List.Sum([Units]).
= Table.Group(Source, {"LocalString"}, {{"Sum", each List.Sum([Sum])/List.Sum([Units]), type number}})
Hi Sauber,
You could try below M code to see whether it work or not
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsxNLcpMTlTSUTIyBRKGBmCWgVKsDlCutLikKDEnEyRrDMSmIElDmCRco4UlSA9YztwCKBkLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [LocalString = _t, Units = _t, Value = _t, Sum = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"LocalString", type text}, {"Units", Int64.Type}, {"Value", Int64.Type}, {"Sum", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"LocalString"}, {{"total sum ", each List.Sum([Sum]), type number}, {"total unit", each List.Sum([Units]), type number}, {"all", each _, type table [LocalString=text, Units=number, Value=number, Sum=number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [#"total sum "]/[total unit]),
#"Expanded all" = Table.ExpandTableColumn(#"Added Custom", "all", {"Units", "Value", "Sum"}, {"all.Units", "all.Value", "all.Sum"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded all",{"total sum ", "total unit"})
in
#"Removed Columns"
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
The code below should do the trick. Just adapt your List.Average to List.Sum([Sum])/List.Sum([Units]).
= Table.Group(Source, {"LocalString"}, {{"Sum", each List.Sum([Sum])/List.Sum([Units]), type number}})
@Anonymous Thanks, that worked perfectly !
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.