Grouped Bar with two categories and trend line

I am trying to create a bar chart with two categories, quarter and region, and then show a trend for each region across quarter. I am able to that by using a Grouped Bar chart with 2 Categories and a Series. But then I need to add a line that shows the average across quarters for each region. The chart would look like this.

I am able to create this chart shown below in Domo:


..but now need to add the average line across quarters for each region, the red line that I've drawn in. Can this be done in Domo?

Comments

  • amehdad
    amehdad 🟒

    Hi @AttuAk, I'm curious, what chart type did you use to create that view (with the side-by-side regions) in Domo?

    If you are after a simple average line, you can use the Scale Marker feature in 'Chart Properties' [https://domohelp.domo.com/hc/en-us/articles/360042925194-Adding-Scale-Markers-to-Your-Charts]. If it's a dynamic average line you need, you can create a beast mode using the Window function, something like:

    SUM('Lead Value') / SUM( SUM(`Lead Value`))Β 

    OVER(PARTITION BY `Region`)

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟀

    Many Bar charts in Domo support Trellis (search for trellis, https://domohelp.domo.com/hc/en-us/articles/360043439893-March-2019-Release-1)


    But, Domo doesn't really support an X axis with two metrics. Or at least. I haven't gotten it to work. I feel like it should be possible though.

  • AttuAk
    AttuAk 🟑

    Hi @amehdad Thanks for your input here. I am using a Grouped Bar chart where Lead Value is the Value and then I use Quarter and Region as the series. This allows for the graph I have above but I still cannot insert another value which would be the average of the lead value by Region.

    When using your suggested beast mode I get an average over the entire region, but I need it by Region and then averaged over the 4 quarters shown. And then I need to add it to the chart as another value. I'm wondering if Domo can support this?

  • amehdad
    amehdad 🟒

    There doesn't seem to be a way currently to have a dynamically split average line across different sections of a graph. You'll need to create duplicate graphs for each region and have them side by side in your dashboard so it looks similar to your very top chart.

  • amehdad
    amehdad 🟒

    Hi @AttuAk, how did you go with this?Β