I'm building a card that displays the average value of a field in single bar graph. I'd like to limit the number of bars that are shown by using the "Maximum Bars" option in the chart properties and use the "Other" bar to show the average of all the remaining values. However, the "Other" bar appears to add up the averages of the bars that are hidden instead of taking the true average, which is not a meaninful metric. Does anyone know a way around this? Thanks!
@MichelleH Mind sharing a screenshot of your settings and the graph?
Thank you for the quick response. I've attached screenshots from the Analyzer. The only Chart Properties option I've configured is the "Maximum Bars". Also, the Beast Mode calculation for the Y-axis is the following:
AVG(DATEDIFF(`Date Col1`,`Date Col2`))
Interesting. I wonder if this is a limitation of the "Other" bar.
Could you try changing the beastmode to:
(SUM(DATEDIFF(`Date Col1`,`Date Col2`)) / COUNT(`Date Col1`))
Are either of the two date columns ever null?
You could also try:
AVG(case when `Date Col1` is not NULL AND `Date Col2` is not NULL then DATEDIFF(`Date Col1`,`Date Col2`) end)
I suspect it is a limitation of the "Other" bar. I've tried formulas similar to what you suggested and it all shows the same result. It seems like the "Other" bar is only intended for summed values.
Thank you for your help.