I created a vertical stacked bar chart showing average numbers across four categories. In addition I want to add a reference line that shows the total average across my categories.
When looking at the underlying data, the calculated reference line does not show what I expect (i.e. summing up the values of each category and then dividing the sum by the amount of categories).
I appreciate any insights for how the calculation is done and if I need to change something to make it work as expected.
To get what you want I would make a calculated field that takes the sum of redaction_time_secs and divides it by the distinct count of your field categories partitioned by the date. However, you will need an exact date field to use that is grouped to your requested date.
calculated field = sumOver({Redecation_Time_Secs},[{date}],PRE_AGG)/distinctCountOver({category},[{date}],PRE_AGG)
The bold field needs to come from the dataset. In this case Jul 2022 would need to come from the dataset because a disctinctCountOver needs to have a partition come from a dataset field.
That should get it to work, but the only issue will be that date field.
Hi, @lirigon. Did the response from @Max answer your question? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community Q&A Forum!
