SUM only Numbers higher than 3

[chrkrg]

Hi.

I am still a complete beginner in Power BI / Query and have not yet found what I am looking for using the search function.

I would like to insert a new column in which the sum of Answer 1 to Answer 3 is calculated if it contains a number greater than 3.

Can anyone help me here?

Thank you!

[m_dekorte · Accepted solution]

Hi @chrkrg,

If your actual data has the same set up as depicted above, try something like this.

let
    Source = Table.FromColumns(
        {
            {1..9},
            {1..3}&{1..3}&{1..3},
            {4..6}&{2..4}&{1..3},
            {2..4}&{1..3}&{4..6}
        }, {"Participant", "Answer 1", "Answer 2", "Answer 3"}
    ),
    AddSumAbove3 = Table.AddColumn(Source, "Custom", each List.Sum( List.Select( List.Skip(Record.ToList(_), 1), each _ >3 )))
in
    AddSumAbove3

When you have other columns as well but they're placed adjacent to each other, try this:

List.Sum( List.Select( List.Range( Record.ToList(_), 1, 3), each _ >3 )))

Or when you need to look up "Answer" fields, try this:

List.Sum( List.Select( Record.ToList( Record.SelectFields(_, List.Select( Record.FieldNames(_), each Text.StartsWith( _, "Answer", Comparer.OrdinalIgnoreCase)))), each _ >3 ))

Ps. If this helps solve your query please mark this post as Solution, thanks!