I am struggling to create a histogram showing the count of an item as % of total across one standard dimension (Year) when the 'buckets' are a calculated dimension. Displaying the actual frequencies works fine but in order to work out percentage of total for a given year, I need to use the statement
count(DISTINCT total <Year> UserID)
Totalling by Year (which is not a calculated dimension) does not seem to work when another calculated dimension is involved, and each row is evaluated as a proportion of itself, leading to all percentages showing as 100%.
See attached simplified example in which I am counting the number of brands shopped with across two years for a handful of users. The frequency chart (top left) works fine but when trying to show it as a % of total this does not work.
Note I need a solution entirely within the chart - I can't consider pre-aggregation in the load script, as my actual case needs to be far more dynamic and I can't pre-calculate every possible option.
