calculate average across columns for all the selected data

[vishnuprashanth]

I have a dataset with 80 columns. 

I have filtered the necessary columns and I am trying to calculate the average for across each column for the selected data.

DATASET:

athlete	time (mm:ss)	Tot Energy	Energy Diff	Total Dist (m)	Acc EV	Dec EV	v<8.00km/h	8.00<v<14.40km/h	14.40<v<19.60km/h	19.60<v<24.00km/h	24.00<v<30.00km/h	v>30.00km/h	max v (km/h)	max P (W/kg)
AKLIOUCHE MAGHNES	06:19	4221	9%	900	0	2	291	386.5	152.8	70.1	0	0	23.24	59.55
ARLOTTI ALESSANDRO	93:19:00	74933	147%	16948	8	33	7290.1	6533.6	2345.8	632.5	145.6	0	28.095	88.675
BOUREBABA ZINEDDINE	14:22	15922	28%	3309	2	7	921.8	1519.4	766.8	90.5	10.6	0	25.25	70.245
DECARPENTRIE VALENTIN	70:47:00	33075	72%	7245	15	20	3008.1	2592.3	983.3	462.4	197.6	0.9	30.04	111.29

OUTPUT EXPECTED:

Average

NaN

32037.75

0.64

7100.5

6.25

15.5

2877.75

2757.95

1062.175

313.875

88.45

0.225

26.65625

82.44

I could achieve this in Excel by the below formula and by dragging it for all my columns. I want to do the same in Power BI.

=AVERAGE(C1:C4)

Output expected in Power BI: 

Output in Power BI

[v-juanli-msft · Accepted solution]

Hi @vishnuprashanth

1. select columns from “Total Energy” to the last one except “athlete” and “time (mm:ss)”

    click Transform->unpivot columns

2. Close and apply, then come back to data model view, create a new table

Table1 = SUMMARIZE(FILTER(Sheet1,[athlete]="AKLIOUCHE MAGHNES"),[Attribute],[avg],[time (mm:ss)],[Value],"as","z-avgall")

3. Union two tables

Table2 = UNION(Sheet1,Table1)

4. Finally, in Report View, use matrix as below

Here is my pbix

Best Regards

Maggie

[vishnuprashanth]

Thank you so much @v-juanli-msft. 

I just tried this and it works perfectly. 

I also found an alternate solution by creating HASONEVALUE function for each column and adding it to the report as a separate table. But your solution works better and is comparatively easier. thanks again 🙂