Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Jeanxyz
Impactful Individual
Impactful Individual

average calculation in power query

Hi,

 

I have a list of employee's FTE rate   (work %) by month. I would like to calculate the average FTE rate (result should look like column D) per employee in power query. how should I write the power query formula?

 

FTE avg.png

 

2 ACCEPTED SOLUTIONS
DataInsights
Super User
Super User

@Jeanxyz,

 

An alternate approach using Group By is to add an aggregation for all rows:

 

1. Group By

 

DataInsights_3-1628519434511.png

 

 

2. Expand the All column and select Date Key and FTE rate:

 

DataInsights_1-1628519301529.png

 

3. Result:

 

DataInsights_2-1628519331177.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

v-luwang-msft
Community Support
Community Support

Hi @Jeanxyz ,

Base data:

vluwangmsft_0-1628750434794.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc5LCgAgCEXRvTgO0teH9hLtfxs5zXw6EQ7C3VvUx6QIFFZ9/TRVOeUj+LlSafypcxqcZiDwQLyBKcQ+8D7wPiR95wI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"employee id" = _t, #"Date Key" = _t, #"FTE rate" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"employee id", Int64.Type}, {"Date Key", type date}, {"FTE rate", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"employee id"}, {{"average", each List.Average([FTE rate]), type nullable number}, {"TABLE1", each _, type table [employee id=nullable number, Date Key=nullable date, FTE rate=nullable number]}}),
    #"Expanded TABLE1" = Table.ExpandTableColumn(#"Grouped Rows", "TABLE1", {"employee id", "Date Key", "FTE rate"}, {"TABLE1.employee id", "TABLE1.Date Key", "TABLE1.FTE rate"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded TABLE1",{"employee id", "TABLE1.employee id", "TABLE1.Date Key", "TABLE1.FTE rate", "average"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"average", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"employee id"})
in
    #"Removed Columns"

Final get:

vluwangmsft_1-1628750528052.png

 

Wish it is helpful for you!

 

Best Regards

Lucien

View solution in original post

3 REPLIES 3
v-luwang-msft
Community Support
Community Support

Hi @Jeanxyz ,

Base data:

vluwangmsft_0-1628750434794.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc5LCgAgCEXRvTgO0teH9hLtfxs5zXw6EQ7C3VvUx6QIFFZ9/TRVOeUj+LlSafypcxqcZiDwQLyBKcQ+8D7wPiR95wI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"employee id" = _t, #"Date Key" = _t, #"FTE rate" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"employee id", Int64.Type}, {"Date Key", type date}, {"FTE rate", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"employee id"}, {{"average", each List.Average([FTE rate]), type nullable number}, {"TABLE1", each _, type table [employee id=nullable number, Date Key=nullable date, FTE rate=nullable number]}}),
    #"Expanded TABLE1" = Table.ExpandTableColumn(#"Grouped Rows", "TABLE1", {"employee id", "Date Key", "FTE rate"}, {"TABLE1.employee id", "TABLE1.Date Key", "TABLE1.FTE rate"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded TABLE1",{"employee id", "TABLE1.employee id", "TABLE1.Date Key", "TABLE1.FTE rate", "average"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"average", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"employee id"})
in
    #"Removed Columns"

Final get:

vluwangmsft_1-1628750528052.png

 

Wish it is helpful for you!

 

Best Regards

Lucien

DataInsights
Super User
Super User

@Jeanxyz,

 

An alternate approach using Group By is to add an aggregation for all rows:

 

1. Group By

 

DataInsights_3-1628519434511.png

 

 

2. Expand the All column and select Date Key and FTE rate:

 

DataInsights_1-1628519301529.png

 

3. Result:

 

DataInsights_2-1628519331177.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




amitchandak
Super User
Super User

@Jeanxyz , You might have create a table using group by avg and join it back with this table

Using group by option, Employee ID as Group and Avg of FTE rate , create a new table. Merge it back using merge option using employee id.

 

Also refer for part of the approch here

https://www.youtube.com/watch?v=EFQBMJ6JyCQ

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.