2 Replies Latest reply on Aug 1, 2013 9:04 AM by Trey Chadwell

    true/false with reference lines

    Trey Chadwell

      Hello everyone,

       

       

       

      I'm working with the Sample - Superstore Subset English test data to make a worksheet using bar charts & references lines. I have attached the workbook I am using, but below is an explanation of what I'm trying to accomplish.

       

      I am displaying the sales in each region, and showing a reference line that shows the average sales across all regions going across the chart. I want the bars to display as red if they are below the average, and green if they exceed the average.

       

      I created the reference line without the use of any calculated fields, and I tried to create a calculated field returning true/false if the region's sales exceeds the average. This calculation is listed below, and is saved under the "Greater than AVG" measure.

       

      SUM([Sales]) > (WINDOW_SUM(SUM([Sales]))/COUNTD([Region]))

       

       

      Am I on the right track, or do I need to go back and have my reference line being based off of a calculated field?

       

       

      Thanks for your help.

       

      -Trey

        • 1. Re: true/false with reference lines
          Joshua Milligan

          Trey,

           

          You are very close.  I believe you just need to change your calculation to:

           

          SUM(Sales) > WINDOW_AVG(SUM(Sales))

           

          What that will give you is a boolean result of true when the sum of Sales for a region is greater than the average of the sum of sales for all regions and false otherwise.

           

          Hope that helps!

           

          Regards,

          Joshua

          • 2. Re: true/false with reference lines
            Trey Chadwell

            Thanks for your help Joshua! Using WINDOW_AVG is a pretty convenient calc to use.

             

             

            Now if I was to add in Departments as a quick filter, is there any way I could show a reference line for the average sales in each department? And then whether or not that department is selected in my quick filter, the reference line would be hidden or visible?

             

             

            I'm assuming instead of doing a pre-generated reference line, I'd have to create a calculated field That is something along the lines of

             

            IF DEPARTMENT = 'Furniture' THEN WINDOW_AVG(SUM(SALES)) END

             

            However, I get an error of "cannot mix aggregate and non aggregate values in an IF..."

             

            Does this mean I need to use more calculated fields in some way?