Hello,
I've attached an excel workbook showing a sample bit in its raw form, what I want to get out of that, and what I am getting.
I'm primarily having two issues:
- Getting everything on 1 row by fiscal week. I can accomplish this until I add a percentage beastmode, then the rows will no longer be condensed to 1 row per week. It changes to 1 row per calculation.
- Getting the table total to function like the beastmode, taking the total performed divided by the total projected, rather than a sum of the beastmode rows.
One thing I tried was modifying my beastmode to a partition by week, recruiter, rather than "performed"/"projection"
This was helpful in the sense that, even after the rows became uncondensed, it still gave the % by week, rather than by row. But this still leaves me with uncondensed rows and doesn't help my total row issue.
The other thing i tried was modifying the beastmode to only partition the denominator by week and recruiter. Then, I could just collapse the rows for week % totals. But this also did not resolve my final, bottom total row. The final bottom total row still SUMs.
This would be a lot easier to accomplish if they did not need it in a table card and I could use bar graphs and separate cards for week total and year total, etc., but thats how they want it so I am doing my due dilligence.
They're also going to want to be able to filter by different procedure codes, so that is why I'm hesitant to attempt this in ETL with group bys or rank and windows
I hope this makes sense