Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Rowwise Dynamic Average of values

Hi,

I have a table like below:

 

 

I want to calculate row wise average of Value 1,2,3,4 in column Average.

If the value is blank or null then it should be ignored.

Need Help
  • ReneSchlegel's avatar
    ReneSchlegel
    3 years ago

    Hi,

    this solution would work if there are only numbers provided in the columns. As Onkar already mentioned there will be some blank or null values. These values would cause an error, because they cannot be transformed into numbers automatically.

    I suggest adding a new column with a custom method using the advanced editor:

    Table.AddColumn(
    <Previous_Step>,
    "Average",
    each List.Average(
        List.Select(
            Record.ToList(
                Record.SelectFields(_, {"Value 1", "Value 2", "Value 3", "Value 4"})
            ),
            each Value.Is(Value.FromText(_), type number)
        )
    )
)

    Please note that you have to replace the placeholder „<Previous_Step>“ with the name of the previous step of your power query script.

3 Replies

  • ray_aramburo's avatar
    ray_aramburo
    Icon for Super User rankSuper User
    3 years ago

    Assuming you'll only use thos 4 columns then create a column with dax like this: Average = ([Value 1] + [Value 2]+ [Value 3] + [Value 4]) / 4

    • ReneSchlegel's avatar
      ReneSchlegel
      Icon for Resolver I rankResolver I
      3 years ago

      Hi,

      this solution would work if there are only numbers provided in the columns. As Onkar already mentioned there will be some blank or null values. These values would cause an error, because they cannot be transformed into numbers automatically.

      I suggest adding a new column with a custom method using the advanced editor:

      Table.AddColumn(
    <Previous_Step>,
    "Average",
    each List.Average(
        List.Select(
            Record.ToList(
                Record.SelectFields(_, {"Value 1", "Value 2", "Value 3", "Value 4"})
            ),
            each Value.Is(Value.FromText(_), type number)
        )
    )
)

      Please note that you have to replace the placeholder „<Previous_Step>“ with the name of the previous step of your power query script.

      • ray_aramburo's avatar
        ray_aramburo
        Icon for Super User rankSuper User
        3 years ago

        Sum does not consider null values when adding, in either case it would be easier to replace the nulls/blanks with a 0 and the result would be the same.