Hi Gavin,
I am thinking if level aware calculations ( Create advanced insights using level-aware calculations in Amazon QuickSight | AWS Big Data Blog ) can help solve your requirements, would be helpful if you can paste sample data and provide examples on the expected results.
an example post where sample was provided in table format ( Help with Rank function
Help with Complex Calc!