mart_mrexcel
Active Member
- Joined
- Aug 23, 2008
- Messages
- 295
- Office Version
- 365
- Platform
- Windows
Given below table. i wanted to take the average of HR in Calculated Field Pivot Table returning the value of 5 instead of 1.66 based on the quarter slicer. I use the formula
1.) =AVERAGEIF(Dept,"<>0") in the calculated field but its giving an error
2.) =AVERAGE(IF(Dept<>0, Dept,"")) but the value is wrong
1.) =AVERAGEIF(Dept,"<>0") in the calculated field but its giving an error
2.) =AVERAGE(IF(Dept<>0, Dept,"")) but the value is wrong
| Date | Dept | Head Count |
| Jan 01, 2021 | HR | 0 |
| Jan 02,2021 | IT | 2 |
| Feb 05,2021 | FINANCE | 4 |
| Feb 25, 2021 | HR | 5 |
| Mar 1, 2021 | FINANCE | 1 |
| Mar 3, 2021 | HR |
