Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
Kaitlyn07
Frequent Visitor

Adding a column with formula to sum columns with a Yes or No criteria

I have merged two data tables using PowerPivot to include information that gives columns a condition (Include Y or No). Now I would like to sum those columns with "Y" criteria for each row. For example:

 

 

Columns to Sum |   A    |    B    |    C    |  New Column (sum of "Y" associated columns) |

Include? (Y or N) |  Y    |    Y    |     N  |        |

Values1  |               1       |     2   |     3    |  = (1+2)= 3 |

Values2  |     2                 |     3   |     4    |   =(2+3)=5  |

etc.

 

I will also note that it is important to retain the Y or N associated with the column so that the column selection used in the formula can be verified. 

 

1 ACCEPTED SOLUTION

Oh no @Kaitlyn07 that was just a sample table.

 

Open the Advanced Editor for the query (table) you want to apply this to. Go to the end of the last line of M code (right above the "in" clause, you will find that on the second last row), place a comma there and press enter to go to a new line. Now copy and paste this code in.

    getCols = (tbl as table, row as number, string as text) as function => 
        (rec as record) => try List.Sum( 
            Record.ToList( 
                Record.SelectFields(rec, Table.SelectRows( Record.ToTable( tbl{row} ), each [Value] = string )[Name])
            )
        ) otherwise null,
    getResult = getCols(Source, 0, "Y"),
    AddSum = Table.AddColumn(Source, "Custom", each getResult(_))

 

Replace "Source" on the last two lines with the name that follows after the "in" clause and then put "AddSum" after the "in" clause in its place. That should do it.


Note that this code below takes a table reference as first argument, a zero based row index of where the Y's and N's are - if on row 1 then subtract 1 and enter a 0 - as second argument and finally the text to match, in your sample that is a: "Y" as third argument. Amend to your needs.

getCols( UpdatedNameHere, 0, "Y")

 

I hope this is helpful.

Please mark as solution when this has helped to resolve your query. Thanks!

View solution in original post

8 REPLIES 8
m_dekorte
Super User
Super User

Hi @Kaitlyn07 

 

Here's an approach, for you to try:

 

let
    Source = Table.FromRows(
        {
            {"Y", "Y", "N"},
            {1, 2, 3},
            {2, 3, 4}
        }, {"A", "B", "C"}
    ),
    getCols = (tbl as table, row as number, string as text) as function => 
        (rec as record) => try List.Sum( 
            Record.ToList( 
                Record.SelectFields(rec, Table.SelectRows( Record.ToTable( tbl{row} ), each [Value] = string )[Name])
            )
        ) otherwise null,
    getResult = getCols(Source, 0, "Y"),
    AddSum = Table.AddColumn(Source, "Custom", each getResult(_))
in
    AddSum

 

 

with this result

m_dekorte_0-1698705546068.png

 

I hope this is helpful

I got this to work with the example data table above. However, I want to apply this to a larger dataset that will change as more data is fetched. Do I need to enter all the row contents for this to work? (see the referenced section below) 

 {"Y", "Y", "N"},
            {1, 2, 3},
            {2, 3, 4}
        }, {"A", "B", "C"}

 

Oh no @Kaitlyn07 that was just a sample table.

 

Open the Advanced Editor for the query (table) you want to apply this to. Go to the end of the last line of M code (right above the "in" clause, you will find that on the second last row), place a comma there and press enter to go to a new line. Now copy and paste this code in.

    getCols = (tbl as table, row as number, string as text) as function => 
        (rec as record) => try List.Sum( 
            Record.ToList( 
                Record.SelectFields(rec, Table.SelectRows( Record.ToTable( tbl{row} ), each [Value] = string )[Name])
            )
        ) otherwise null,
    getResult = getCols(Source, 0, "Y"),
    AddSum = Table.AddColumn(Source, "Custom", each getResult(_))

 

Replace "Source" on the last two lines with the name that follows after the "in" clause and then put "AddSum" after the "in" clause in its place. That should do it.


Note that this code below takes a table reference as first argument, a zero based row index of where the Y's and N's are - if on row 1 then subtract 1 and enter a 0 - as second argument and finally the text to match, in your sample that is a: "Y" as third argument. Amend to your needs.

getCols( UpdatedNameHere, 0, "Y")

 

I hope this is helpful.

Please mark as solution when this has helped to resolve your query. Thanks!

I have another row of criteria that I would like to add a summed column based on the criteria (similar to above). Is there a way I can change the code to reference row 2 of the table for the Y or N criteria for the second condition? 

@Kaitlyn07 

 

Certainly, as I've tried to explain here:

Note that this code below takes a table reference as first argument, a zero based row index of where the Y's and N's are - if on row 1 then subtract 1 and enter a 0 - as second argument and finally the text to match, in your sample that is a: "Y" as third argument. Amend to your needs.

getCols( UpdatedNameHere, 0, "Y")

 

Therefore if you need the 2nd row subtract 1 and enter 1 as second argument, like so:

getCols( UpdatedNameHere, 1, "Y")

Thank you @m_dekorte. I apologize, I am new to power query and the M language and am still learning the logic behind the code. 

 

I got this to work by merging queries with different arguments as you clarified above. Thanks for all of your help!

No worries @Kaitlyn07 

Glad you got this working 👍

Thank you m_dekorte!!!! I have been trying to solve this for weeks and finally got what I need. I cannot thank you enough. 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors