Forum Discussion
Sum on column based on selected row attributes without Grouping
- 2 years ago
Power Query is not designed to do table scans like this. This is much MUCH better in DAX. However...
The last two columns were only necessary for debugging. I also changed your values so one would be < 90%.
I did use the Group By feature and did all the math in the nested tables, then expanded it all back out. I grouped by Entity and Client. If you need to do it by quarter also, then add that aggregation in the code. here is the code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJR8slPzCsG0s45mal5JQqGQGagoYKRgZERkGVoYKAUq4NPqRFIqTGQZWSKrNQ5Pzc3syQXqIw4o3Gqh5uP5hR/Z1wGGxFQiHAxikLHnByFgKL8lNLkEnzBQaQWuCVmKDr88vOSi1JTMnGGCnHK4aab4okeIxSDTQgGtxGewEEJRSP0MCEYJGg6TAl5EtUpYBtiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Entity = _t, Product = _t, Client = _t, Time = _t, #"Total Revenues" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Total Revenues", Int64.Type}}), #"Grouped Rows" = Table.Group( #"Changed Type", {"Entity", "Client"}, // add additional column aggregations here, then exclude them from the expansion in the last step. { {"AllRows", each let varAllProductsRev = List.Sum(Table.SelectRows(_, each [Product] = "All Products")[Total Revenues]), varOtherRev = List.Sum(Table.SelectRows(_, each [Product] <> "All Products")[Total Revenues]) in Table.AddColumn( _, "Credit Only", each if varOtherRev / varAllProductsRev > .90 then "Yes" else "No" ), type table [Entity=nullable text, Product=nullable text, Client=nullable text, Time=nullable text, Total Revenues=nullable number, Credit Only = text] }, { //You can remove this entire section including both {} "All Product Rev", each List.Sum(Table.SelectRows(_, each [Product] = "All Products")[Total Revenues]) }, { //You can remove this entire section including both {} "All Other Product Rev", each List.Sum(Table.SelectRows(_, each [Product] <> "All Products")[Total Revenues]) } } ), #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Product", "Time", "Total Revenues", "Credit Only"}, {"Product", "Time", "Total Revenues", "Credit Only"}) in #"Expanded AllRows"In the AllRows part of the Group By I added two variables to compute the All Products and All Other Products revenue. This will not work with large datasets. It will simply bog down. Again, DAX. But for smaller datasets, it will work just fine. Test on your data.
The AllRows column has the following nested table:
The red columns need to be expanded out, and the Credit Only column is the Yes/No that you want.
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
Power Query is not designed to do table scans like this. This is much MUCH better in DAX. However...
The last two columns were only necessary for debugging. I also changed your values so one would be < 90%.
I did use the Group By feature and did all the math in the nested tables, then expanded it all back out. I grouped by Entity and Client. If you need to do it by quarter also, then add that aggregation in the code. here is the code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJR8slPzCsG0s45mal5JQqGQGagoYKRgZERkGVoYKAUq4NPqRFIqTGQZWSKrNQ5Pzc3syQXqIw4o3Gqh5uP5hR/Z1wGGxFQiHAxikLHnByFgKL8lNLkEnzBQaQWuCVmKDr88vOSi1JTMnGGCnHK4aab4okeIxSDTQgGtxGewEEJRSP0MCEYJGg6TAl5EtUpYBtiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Entity = _t, Product = _t, Client = _t, Time = _t, #"Total Revenues" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Total Revenues", Int64.Type}}),
#"Grouped Rows" =
Table.Group(
#"Changed Type",
{"Entity", "Client"}, // add additional column aggregations here, then exclude them from the expansion in the last step.
{
{"AllRows",
each
let
varAllProductsRev =
List.Sum(Table.SelectRows(_, each [Product] = "All Products")[Total Revenues]),
varOtherRev =
List.Sum(Table.SelectRows(_, each [Product] <> "All Products")[Total Revenues])
in
Table.AddColumn(
_,
"Credit Only",
each if varOtherRev / varAllProductsRev > .90 then "Yes" else "No"
), type table [Entity=nullable text, Product=nullable text, Client=nullable text, Time=nullable text, Total Revenues=nullable number, Credit Only = text]
},
{
//You can remove this entire section including both {}
"All Product Rev",
each List.Sum(Table.SelectRows(_, each [Product] = "All Products")[Total Revenues])
},
{
//You can remove this entire section including both {}
"All Other Product Rev",
each List.Sum(Table.SelectRows(_, each [Product] <> "All Products")[Total Revenues])
}
}
),
#"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Product", "Time", "Total Revenues", "Credit Only"}, {"Product", "Time", "Total Revenues", "Credit Only"})
in
#"Expanded AllRows"
In the AllRows part of the Group By I added two variables to compute the All Products and All Other Products revenue. This will not work with large datasets. It will simply bog down. Again, DAX. But for smaller datasets, it will work just fine. Test on your data.
The AllRows column has the following nested table:
The red columns need to be expanded out, and the Credit Only column is the Yes/No that you want.
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.