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?