6 Replies Latest reply on Sep 20, 2016 6:55 AM by remyakrishnan.v

    Combining two tables into one bar graph?

    Ryan Jablon

      How do you combine two tables of data into one bar graph? I am trying to display FY 2015 baseline data as one bar and then my other two bars are FY 2016 Q1 and FY 2016 Q2. I had created a relationship between the two tables and then did dual axis and synchronized the graphs however the data then lays on top of the FY 2016 data. How can I get the FY 2015 data as a bar next to the other two bars in the graph?

        • 1. Re: Combining two tables into one bar graph?
          swaroop.gantela

          Ryan,

           

          I'm not sure if this quite gets you there,

          but you could maybe try putting Measure Values on your Row/Column shelf,

          and then pulling in each of the amounts from your two sheets.

          You could Group them just by the Quarter (not including the year) to get them side-by-side.

           

          210150bar.png

          • 2. Re: Combining two tables into one bar graph?
            Ryan Jablon

            Hi Swaroop,

            Thank you for your response. My one major difference is I'm dealing with a baseline of an entire year being 2015. It is not broken down into quarters and I'm trying to plot that along side 2016 Q1 and Q2.

            • 3. Re: Combining two tables into one bar graph?
              Ryan Jablon

              Thank you for your response. My one major difference is I'm dealing with a baseline of an entire year being 2015. It is not broken down into quarters and I'm trying to plot that along side 2016 Q1 and Q2.

              • 4. Re: Combining two tables into one bar graph?
                swaroop.gantela

                Ryan,

                 

                This doesn't look great, but you can maybe try some things with WINDOW_SUM

                to aggregate the 2015 data.

                 

                Also, these links may have some useful ideas for you:

                kettan's excellent posts on YOY YTD bars

                YOY YTD Bar Chart

                Fiscal YOY YTD Bar Comparison

                 

                210150barB.png

                1 of 1 people found this helpful
                • 5. Re: Combining two tables into one bar graph?
                  swaroop.gantela

                  Ryan,


                  Here is another stab at it.

                   

                  You can fix the 2015 amount using on the 2015 sheet:

                  { FIXED [YearOfDate]:SUM([Value])}

                   

                  Then on the 2016 sheet, you can calculate individual quarter amounts:

                  IF YEAR([Date])=2016 AND DATEPART('quarter',[Date])=3

                  THEN [Value]

                  END

                   

                  And then you can put all of these on the Measure Values Shelf.

                   

                  210150barC.png

                  • 6. Re: Combining two tables into one bar graph?

                    These are the following procedure will help you create a combination chart , For this chart, we used the example worksheet data.

                    Create a blank workbook or worksheet.

                    • Select the example in the Help topic.

                    NOTE: Do not select the row or column headers.

                    Selecting an example from Help

                    • Press CTRL+C.
                    • In the worksheet, select cell A1, and press CTRL+V.

                    a. Select the data that you want to plot in the combination chart.

                    b.       On the Insert tab, in the Charts group, click Column.

                    c. Under 2-D Column, click Clustered Column.

                    d.       In the chart, click the data series that you want to display in a different chart type, or select it from a list of chart elements (Layout tab, Current Selection group, Chart Elements box).

                    For our combination chart, we selected the data series for Average Price.

                    This displays the Chart Tools, adding the Design, Layout, and Format tabs.

                    e.       On the Design tab, in the Type group, click Change Chart Type.

                    NOTE: If the whole chart changes to a line chart, you need to make sure that only one data series is selected before you change the chart type.

                    f. Under Line, click Line with Markers, and then click OK.

                    g.       To plot the line on a secondary axis, do the following:

                      1. i. In the chart, click the line that represents Average Price once to select the data series, or select it from a list of chart elements (Layout tab, Current Selection group, Chart Elements box).
                      2. ii. On the Layout tab, in the Current Selection group, click Format Selection.

                    iii. In the Series Options category, under Plot Series On, click Secondary Axis, and then click Close.

                    h.       Click the chart area of the chart.

                    i. On the Design tab, in the Chart Styles group, click the chart style that you want to use.

                    For our combination chart, we used Style 42.

                    j. To change the size of the chart, on the Format tab, in the Size group, select the shape size that you want in the Shape Height and Shape Width boxes, and then press ENTER.

                    k.       For our combination chart, we used 5 for the shape height and 5 for the shape width.

                    TIP: You can also resize a chart by dragging one of the corners of the chart until the chart reaches the size that you want.

                    l. To add, format, and position a chart title on the chart, click the chart area, and then do the following:

                      1. i. On the Layout tab, in the Labels group, click Chart Title, and then click Above Chart.

                    ii. In the chart, click the chart title, and then type the text that you want.

                    For our combination chart, we typed Recent Home Sales.

                    1. iii. To reduce the size of the chart title, right-click the title, and then enter the size that you want in the Size box on the shortcut menu.

                    For our combination chart, we used 18.

                    1. m.     To move the legend, do the following:
                      1. i. Click the legend to select it.
                      2. ii. On the Layout tab, in the Labels group, click Legend, and then click the position that you want.

                    For our combination chart, we clicked Show Legend at Bottom.

                    1. n.       To add vertical axis titles, do the following:
                      1. i. On the Layout tab, in the Labels group, click Axis Titles, and then do the following:
                        • Click Primary Vertical Axis Title, and then click the title option that you want.
                        • Click Secondary Vertical Axis Title, and then click the title option that you want.

                    For our combination chart, we clicked Rotated Title for both axes.

                    1. ii. Click each axis title, and then type the text that you want for that title.

                    For our combination chart, we typed Number of homes for the primary vertical axis title and Average price per home in thousands for the secondary axis title.

                    1. iii. To change the font size of the axis titles, click each axis title, and then click the size that you want in the Font Size box.
                    2. iv.
                    • o. To change the appearance of the markers that are displayed on the Average Price line, do the following:
                      1. i. Right-click a marker, and then click Format Data Series on the shortcut menu.
                      2. ii. Click Marker Options, and then under Marker Type, click Built-in.
                      3. iii. In the Type box, click the marker type that you want to use.

                    For our combination chart, we used a round marker type.

                    1. p.       Click the chart area of the chart.
                    2. q.       On the Format tab, in the Shape Styles group, click the More button  , and then click the effect that you want to use.

                    For our combination chart, we used the Subtle Effect - Dark 1 for the chart area.

                    1. r. If you want to use theme colors that are different from the default theme that is applied to your workbook, do the following:
                      1. i. On the Page Layout tab, in the Themes group, click Themes.
                    1. ii. Under Built-in, click the theme that you want to use.

                    For our combination chart, we used the Office theme.

                    Save a chart as a template

                    If you want to create another chart like the one that you just created, you can save the chart as a template that you can use as the basis for other similar charts.

                    1. 1.       Click the chart that you want to save as a template.
                    2. 2.       On the Design tab, in the Type group, click Save as Template.
                    3. 1.       In the File name box, type a name for the template.

                    For understanding more you can go in refer this <cloaked advertising removed>.

                    1 of 1 people found this helpful