"Other" bar for averages

MichelleH
MichelleH Coach
in Charting

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!

Comments

  • ST_-Superman-_
    ST_-Superman-_ Coach

    @MichelleH Mind sharing a screenshot of your settings and the graph?


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • MichelleH
    MichelleH Coach

    @ST_-Superman-_

    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`))
  • ST_-Superman-_
    ST_-Superman-_ Coach

    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)

    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • MichelleH
    MichelleH Coach

    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.

This discussion has been closed.

Categories