2 Replies Latest reply on Jun 17, 2016 1:38 AM by Marc Levy

    Finding Difference From Reference Line and Coloring Above & Below Line

    Marc Levy

      I'd like to calculate the difference from the window average for each product subcategory in the attached workbook.  In other words, the difference from the window average, and then if the bar falls to the right of the line (above) average, then it would be green, and if the bar falls below the line, it would be colored red.

       

      I'd like the "average" reference line to serve essentially as the axis, where the bars are measured against the reference line RATHER than as it is currently displayed, starting at '0' on the axis.

       

      An explanation and/or a revised workbook with the solution would be fantastic.

        • 1. Re: Finding Difference From Reference Line and Coloring Above & Below Line
          Jennifer Petti

          Hi Marc,

          To get the difference between sales and the average of those sales, you'll use a calculated field to subtract the average from the sum.  Then you'll have a positive or negative difference.

          Then build out your visualization using this metric on columns, and color the bars as you desire.

           

           

          If you'd like your view to have the average value as your center point of the axis, that can be achieved.  Start with Sales on Columns and Sub-Category on Rows, but instead of displaying this as a bar chart, choose Gantt Bar.  You can make this change on the Marks card.

           

          This gives a line for every Sales value.  Then you'll want to size the bars based on the difference from the average.  Drag the Difference from Average calculation created in the beginning, to the size shelf on the marks card.

          Our view now looks like this:

           

          To make your average value be the center, use the inline calculation editor by double clicking on the Difference from Average pill on the marks card, and add a negative to beginning.

           

          Now your view is identical to the first visualization created, but the center point is the total average of sales.

          I hope this helps! I'm attaching your workbook with the additional visualizations.

          Cheers,

          Jenn