Calculate average across multiple columns in same row across entire table in Power BI

Question

I would like to calculate the daily average weight gain per day for each row and add the answer in a a new column.

The variable to calculate the average are located all in the same table (Raw Data) and same row but throughout multiple columns.( Body Weight Yest 1, Body Weight Yes 2).

Not all data is available. How do I calculate average across multiple columns in same row in Power Bi?

I have solved the issue by creating following columns with formulas:

Total Across Columns

Total Across Columns= [Body Weight Yest 1.] + [Body Weight Yest 2.]

Non Blank Columns

Non Blank Columns=
IF ( ISBLANK ( [Body Weight Yest 1.] ), 0, 1 )
  + IF ( ISBLANK ( [Body Weight Yest 2.] ), 0, 1 )

Body Weight Daily AVG.

Body Weight Daily AVG = [Total Across Columns] / [Non Blank Columns]

in Power Query (Excel Add On):

=IFERROR(AVERAGEIF(Raw Data[@[ Body Weight Yest. 1]:[ Body Weight Yest. 2]];"<>");"")

I would like to know if there is a simpler/better solution?

Answer 1

If you can restructure your model, the approach I would take is to unpivot your data with Table.Unpivot in M:

let
  <your existing query>,
  Unpivoted = 
    Table.Unpivot(
      <your prior last step>,
      {"Body Weight Yest 1", "Body Weight Yes 2"},
      "Measurement",
      "Value")
  RemoveNulls = Table.SelectRows(Unpivoted, each [Value] <> null)
in
  RemoveNulls

This would take your multiple columns and make them rows. The column headers are now values in the new column, [Measurement], and the values which were in the individual columns are now all in the new column, [Value], and have the appropriate label in the [Measurement] column.

Now, you can define your average as follows:

Total body weight = SUM ( 'Raw Data'[Value] )

Measurement count = DISTINCTCOUNT ( 'Raw Data'[Measurement] )

Average measurement = DIVIDE ( [Total body weight], [Measurement count] )

In general, unpivoting is a very powerful transform and very useful to help DAX get more efficient and easier to write. Tall tables are good tables.