An LOD use case.
If you calculate % of total with a table calculation…
…it looks fine whilst you’ve got all the things in the view…
…but as soon as you apply a filter it breaks down…
This happens because table calculations are only applied to the things in the view. By filtering onto one apparatus, we leave only that apparatus in the view, so of course it makes up 100% of its own total. The problem here is that by filtering onto one piece of apparatus, we change the denominator of the fraction from the overall total to just the total of the apparatus in the view.
Now, how do we get around this problem? LOD calculations! Don’t believe a word Joe says – they’re not fun (yet… I say that after one day of intensive, brain-destroying LOD education). But they’re useful, so we must continue to fight through the hard times and the sadness that it takes to learn them.
The key here is fixing the denominator of your fraction as the overall total of whatever you’re trying to find the percentage of total for. So, in this case, we need to fix the denominator as the athlete’s overall total, so that the calculation stays fixed as (Apparatus Total/Overall Total).
Initially this looks very similar to the table calculation version…
…but once we dig a little deeper we can also get a nicely filtered view!
Now I’d love to write a cure-all generalised formula for something like this, but sadly I think the answer would depend on what you’re trying to achieve. If I tried (which I did, in my notebook) I’d go for something like this:
(SUM Y)/({FIXED X: SUM Y})
I use the X and Y terminology in those positions purposefully, because the thing that you want to fix on in this use case will probably be on the X axis, whilst the thing you’re summing is likely to be on the Y axis. Hopefully. Good luck!