Forum Discussion
Calculate column level average by multiple category
Hi Team,
help to add column to calculate average by multiple category.
Here i want to calculate the Average of logged hours by adding new column for the criteria, Outliers should be no, group by Employee ID & Converted month
| employeeid | monthName | accountName | conloggedhrs | PTOS | converted month | contotalProdHours | Outlier | Cuculate Column (Logged) |
| 866582 | Nov | Abbvie PV
| 1.44 | 0.13 | 01-Nov-23 | 0.13 | Yes | |
| 866582 | Nov | Abbvie PV | 11.78 | 11.53 | 01-Nov-23 | 11.53 | No | 7.73 |
| 866582 | Nov | Abbvie PV | 9.98 | 9.66 | 01-Nov-23 | 9.66 | No | 7.73 |
| 858516 | Nov | Abbvie PV | 0.84 | 0.48 | 01-Nov-23 | 0.48 | Yes | |
| 858516 | Nov | Abbvie PV | 0.66 | 0.59 | 01-Nov-23 | 0.59 | No | 3.33 |
| 858516 | Nov | Abbvie PV | 0.5 | 0.23 | 01-Nov-23 | 0.23 | Yes | |
| 866582 | Nov | Abbvie PV | 2.33 | 0.13 | 01-Nov-23 | 0.13 | Yes | |
| 866582 | Nov | Abbvie PV | 1.44 | 10 | 01-Nov-23 | 11.53 | No | 7.73 |
| 866582 | Nov | Abbvie PV | 9.8 | 10.3 | 01-Nov-23 | 9.66 | Yes | |
| 858516 | Nov | Abbvie PV | 6 | 2.4 | 01-Nov-23 | 0.48 | No | 3.33 |
| 858516 | Nov | Abbvie PV | 4.5 | 0.59 | 01-Nov-23 | 0.59 | Yes | |
| 858516 | Nov | Abbvie PV | 3.7 | 0.23 | 01-Nov-23 | 0.23 | Yes |
| Criteria | Cuculate Column (Logged) |
| Outlier | No |
| Group By | Employee ID, Converted Month |
2 Replies
ssk_1984 , Try a new column like
averageX(filter(Table, [Employee ID] = earlier( [Employee ID] ) && [Converted Month] = Earlier([Converted Month]) ), [Logged])
or
if([Outlier] = "No", averageX(filter(Table, [Employee ID] = earlier( [Employee ID] ) && [Converted Month] = Earlier([Converted Month]) ), [Logged]), blank())
Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8
https://www.youtube.com/watch?v=cN8AO3_vmlY&t=17820s
