Forum Discussion

chrkrg's avatar
chrkrg
Regular Visitor
3 years ago
Solved

SUM only Numbers higher than 3

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!

 

 

Need Help
  • m_dekorte's avatar
    m_dekorte
    3 years ago

    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!

1 Reply

  • m_dekorte's avatar
    m_dekorte
    Resident Rockstar
    3 years ago

    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!