7 Replies Latest reply on Jan 6, 2014 7:51 AM by Jim Wahl

    Add average into chart when adding reference line isn't available

    Joan Dalpe

      Happy New Year's Eve!

       

      I have this kind of chart which doesn't allow adding a reference line ( I know I'd be better off sending the workbook but it would take too long to blind it). I want to include the overall average score for each row year ( a number that reflects the average of the circle placed somewhere around where the axis is 1). Seems like there is an easy way and an advanced way to do this, but I can't seem to get anywhere either way. If the simple way is a calculated field for each year that returns the average score then I can't seem to write it correctly. Then I've seen Jim Wahl create extra columns and invisible reference lines, but this kind of chart doesn't allow adding reference lines. I feel like I'm missing the obvious, but then I'd have to have more advanced skills for much to be obvious anyway... I would really appreciate someone pointing me in the direction to figure it out.

      Add reference room.PNG.png

      Uhhh...

      Uhh....PNG.pngJim Wahl

        • 1. Re: Add average into chart when adding reference line isn't available
          Joshua Milligan

          Joan,

           

          You should be able to use the WINDOW_AVG calculation to get the result you desire.  Try something like:

           

          WINDOW_AVG([Criteria Score])

           

          You'll need to set the "Compute Using" to use whatver dimension is on color.  If there are other dimensions defining the level of detail, those will also need to be included (that's where having a screenshot that includes the fields in the view or, even better, a packaged workbook would help greatly).

           

          If you're still stuck, please mock-up an example and I'd be happy to take a look!

           

          Happy New Year!

          Joshua

          1 of 1 people found this helpful
          • 2. Re: Add average into chart when adding reference line isn't available
            Jim Wahl

            One approach is to use a dual-axis with a set of marks for the labels. To create the below, I

            • Created a calculated field Label Position = 0.0 --- since the SUM(Sales) axis is floating point, the Label Position axis must also be floating point to enable the Synchronize Axis option. You can of course adjust this value to adjust the position of the label.
            • Created another calculated field Label Value = ... Here I used WINDOW_AVG(SUM(Sales)), because I wanted an aggregate of an aggregate (AVG of SUM(Sales)). If your data isn't aggregated, you might be able to use AVG(Sales), which would be calculated for each pane (panes are created by the blue pills Sub-category and Year in my example)---you don't need to create a formula (IF YEAR==2011 THEN) to do this.
              • I also had to wrap the WIDOW_AVG(SUM(...)) in an IF FIRST() == O THEN ... END clause, since I only want one value per mark.
            • On the Label Position Marks card,
              • Drag Label Value to the Label button. Click the Label button format the labels.
              • Change mark type to circle, Color to 0% transparency.

             

            You'll have to play with the label position and formatting. If your data allows, perhaps use a fixed axis to prevent formatting issues when / if the chart is resized.

            2013-12-31 21-28-32.png

            • 3. Re: Re: Add average into chart when adding reference line isn't available
              Jim Wahl

              Either I'm smarter in the New Year or less tired. Either way, the simpler approach to adding the AVG(...) calc next to the year is to just use a discrete blue pill, which will create a pane left of the measure.

               

              It's still a WINDOW_AVG() calc, since you want to aggregate the values across all products to get the AVG SUM(Sales) for each Category, Year dimension. But you don't want the IF FIRST() == 0, since this will generate null rows.

               

              2014-01-01 08-28-10.png

              • 4. Re: Re: Add average into chart when adding reference line isn't available
                Joan Dalpe

                This has been a fun puzzle to work on post New Year. Unlike you, I'm not feeling smarter however since my critical pill won't be blue and I can't make limit the calculation to across and not down. I'm interested in both of your solutions and will use them all the time once I get this figured out....I'm still working on it though.

                • 5. Re: Add average into chart when adding reference line isn't available
                  Joan Dalpe

                  Okay - So now I've changed my continuous calculation to discrete like Jim Wahl did in his Dotcart workbook. The main difference I'm experiencing is that my calculation formulates across then DOWN no matter what I do. So I'm not getting anything like his neat and tidy averages of all the data points across. My head is all fogged up with this problem...

                  • 6. Re: Add average into chart when adding reference line isn't available
                    Shawn Wallwork

                    Do you have the equivalent of "Product Name" on the Detail shelf? If you do then you should be able to use it for compute using. Across/down pane down, etc are all relative compute using and can/will change the calculation when you move the pills around. Where as if you use specific compute using like Jim did then you will get more predictable results.

                     

                    Hope this helps.

                     

                    --Shawn

                    • 7. Re: Add average into chart when adding reference line isn't available
                      Jim Wahl

                      Hi Joan,

                       

                      Happy New Year. Did you get this figured out?

                       

                      I'd be happy to do a short screen sharing session if you're still stuck.

                       

                      Jim