Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 !