Hi,
I’m having trouble calculating the total FTEs by quarter using a Pivot Table from Apr-June—it’s not giving the correct results. I’m wondering if using DAX would be a better approach for this.
Specifically, I want to calculate the FTE for Architecture based on the formula:
Total Hours (1033) ÷ (40 hours × 13 weeks)
Could you advise on the best DAX measures to use for this kind of calculation or what could be the steps I can follow to get the correct calculations?
Thank you in advance for the help! I've been stuck with this for a week
@riny thank you. I have splicers connected to the Pivot. Will it dynamically work if I go back to choosing per month
I want the report to be dynamic where if I select the month in the slicer, it will show the correct FTE total for the month and if I select 3 months in the slicer, it will show the correct calculation. So far it is not giving the correct calculations.
Thanks in advance for the response! 🙂
So you want to calculate FTE's based on whatever period you select? One month, a quarter, Feb to Maj or whatever? Not sure. Can you share an anonymised file?
Yes. Please see the anon file attached. let me know if you are having issues with the file. Again, thanks!
Thanks for the file. But please create the pivot table how you would like it and add the slicers you need. Make some relevant selections in the slicers and indicate what FTE numbers you expect as a result in that particular view. Explain the logic if not obvious.
Now sheet 2 seems to be a rough set-up of where you want everything to go and some example formulas to the far right.
Please see the attached file with pivot and slicers
Notes:
- The FTE calculation works fine for each month.
- When I try to get FTEs for custom periods (like Jan–Aug or by quarter), the numbers aren’t right.
For example, if I pick Jan–Aug (35 weeks), the FTE should be: Total hours ÷ (40 × 35) So, for 1033 hours: 1033 ÷ (40 × 35) For a quarter (13 weeks): Total hours ÷ (40 × 13)
Ideally, when I use the slicers to select a custom period, the FTE should update based on the number of weeks in that range—not just show the monthly breakdown
I am hoping to have the FTE to dynamically adjust according to the total weeks selected or for the selected date range, using the formula above.
Thank you for the help and let me know if you need more details
@bee I created a small scale model in Power Pivot and believe it illustrates yuor problem and how you can resolve it.
@riny Hi, I’m running into an issue with my slicer and the pivot info for the file I mentioned a couple of weeks back. When I input the new hours for September, a blank option shows up in the slicer, and it’s messing with my pivot data. Can you help me figure out what I did wrong this time? I just followed the DAX from the sample file you sent me. Thanks!
Difficult to help without seeing the file. Can you replace any confidential info with fake info?
