Forum Discussion
List.Average and Select rows
Hello,
could someone help me with this problem?
I am trying to add this formula =List.Average(Table.SelectRows(#"added custom6", each [roles] = _[roles])[wage])
I have this table
| ROLES | WAGE |
| role1 | 10 |
| role1 | 11 |
| role2 | 10 |
| role2 | 5 |
| role3 | 5 |
| role3 | 8 |
And I want to add third column with that formula, it should do average of every specific role.
It should look like this:
| ROLES | WAGE | AVG WAGE |
| role1 | 10 | 10,5 |
| role1 | 11 | 10,5 |
| role2 | 10 | 7,5 |
But that formula is doing an average of every position, not the specific one... so it look like this..
| ROLES | WAGE | AVG WAGE |
| role1 | 10 | 8,17 |
role1 | 11 | 8,17 |
| role2 | 10 | 8,17 |
14 Replies
Anonymous , You should create a meausre in DAX
Measure =
Average(Table[WAGE])
Anonymous , A calculated column DAX or Power Query will sum up entire column unless you put filter, while measure will group for the context,
refer
Complete Power BI in one Video 11 hours:
https://www.youtube.com/watch?v=cN8AO3_vmlY
Hi Anonymous
You need to assign the outer ROLE column to a variable like below:
let role = [ROLES] in List.Average(Table.SelectRows(#"Changed Type",each [ROLES] = role)[WAGE])let role = [ROLES] in List.Average(Table.SelectRows(#"Changed Type",each [ROLES] = role)[WAGE])I simply updated this part of your formula with the correct syntax:
each [roles] = _[roles]
[ROLES] is a column assigned to the role variable. This should be done whenver you're trying to access a column outside the current scope which in this case is #"Changed Type". Whatever the value of ROLES is it will be assigned to this variable and will be used to filter the previous step which result is carried over in the current step. By not doing this you are simply telling Power Query to return true for all rows.
