Sum on column based on selected row attributes without Grouping

[mnf1]

Hello,

I am using Power Query in an attempt to calculate whether each client is a Credit Only by summing the Total revenues of LOC, Loans, and Commitment and then dividing that by all Products Total Revenue to see if it exceeds 90% and show this as an added column with a Yes or null result. Because I am doing this at the client levI've struggling to string together proper coding. 

See my sample dataset below.

Entity	Product	Client	Time	Total Revenues
ABC	Loans	Client 1	Q1 2022	100
ABC	Loans	Client 1	Q2 2023	250
ABC	Commitment	Client 1	Q1 2022	100
ABC	Commitment	Client 1	Q2 2023	100
ABC	LOC	Client 1	Q1 2022	200
ABC	LOC	Client 1	Q2 2023	200
ABC	All Products	Client 1	Q1 2022	1000
ABC	All Products	Client 1	Q2 2023	600
ABC	Noncredit	Client 1	Q1 2022	600
ABC	Noncredit	Client 1	Q2 2023	50
ABC	Loans	Client 2	Q1 2022	400
ABC	Commitment	Client 2	Q2 2023	500
ABC	LOC	Client 2	Q1 2022	500
ABC	All Products	Client 2	Q1 2022	1500
ABC	Noncredit	Client 2	Q2 2023	100

Thanks in advance!

[edhans]

@mnf1 did this help?

[mnf1]

Lets say, instead of filtering my data by the code, I'd like to sum where each product is "Loans" and "Loan Commitments" and "LOC". Will the List.Sum function work for this?

 List.Sum(Table.SelectRows(_, each [Product] <> "All Products")[Total Revenues])

[edhans · Accepted solution]

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.