First off, I'm not able to post a workbook as the data to build this view is confidential, but I'll try and give a good enough rundown of the structure of it and where I'm stuck. Working with monthly data, everything is as expected, but going quarterly I cannot get to what I need.
My primary data source is a file that contains FTE information for each branch by month. Each branch is within an area, then a region, and a division. It is blended with a case incident file which can contain 0 to many records per branch per month. I've blended because my goal is to figure out the total FTE per level and the total cases per level. Left joins caused duplication in the FTE data as there could be several cases in a given AU. My viz is the Period and Division on rows and SUM(FTE) and SUM(Cases) for columns. All works great looking at months.
When I move to try and summarize data quarterly, at the moment I'm trying to get the average FTE per division per month within a quarter, eventually also calculate cases per FTE within the quarter. We don't want to count the FTE's 3 times as this is a quarterly calculation hence my need for averages. Due to blending, I cannot divide by COUNTD(Period) or anything similar that I have tried to mimic. Hardcoding to 3 is fine, but I feel like that isn't the best way to solve my issue if for some reason we go annually or something like that. I'd have to manually update all of the calculations. I think there should be a way to make this function. I've tried table calcs and I just get weird numbers that I cannot figure out where they came from. If I take the AVG(FTE), it just gives me the average per branch in each region tallied up to the division. Which isn't right either.
If necessary, I can try to make a scrubbed version to post, but just wanted to present the problem first and see if it's a common problem that I haven't managed to find the solution for. Is it not easily solvable with Tableau like some posts I read indicated?
Thanks for any help that is provided!