Forum Discussion
Helper IIIcalculate average across columns for all the selected data
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
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
2 Replies
v-juanli-msftCommunity Support
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
vishnuprashanthThank 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 :)