2 Replies Latest reply on Jun 8, 2018 3:04 AM by Sean Tobin

    Invidiual scores and Group average scores in one Chart

    Sean Tobin

      Hello, I am trying to re-create an excel visualization that puts an individuals scores in a bar chart then adds a point of reference for the group's average.

       

      The chart should look like this:

       

      Screen Shot 2018-06-08 at 12.34.16 AM.png

       

      I have attached what the data looks like. The problem I am having is getting a viz to show both scores. The closest I can get so far is an individuals scores and their own average using measure names and values. I am unsure how to add the overall average to the bar chart. Any help would be appreciated. Thanks!

        • 1. Re: Invidiual scores and Group average scores in one Chart
          Mavis Liu

          Hi Sean,

           

          It looks like you'll have to re-shape the data a bit.

           

          So first pivot the data by highlighting all the measure columns and right click to select pivot:

           

          2018-06-08_09h35_35.png

           

          You'll now see two new columns - Pivot Field Names and Pivot Field Values.

          The Pivot Field names are contains the groups and Q period. Right click on the column and do a custom split based on the full stop to bring back the first and last columns:

           

          2018-06-08_09h40_32.png

           

          Rename your columns as appropriate:

           

          2018-06-08_09h41_51.png

           

          Now go into sheet one and we can start building the viz.

           

          So we need Group and Period in the columns, then sum(value) and avg(value) in the rows:

           

          2018-06-08_09h43_24.png

           

          Right click again and select Dual Axis.

           

          Change one of the sum of values marks card into a bar:

           

           

          2018-06-08_09h44_25.png

           

          and make sure the avg values marks type is circles. Then right click on the secondary axis and synchronise them, you can also hide the header afterwards (there's no need to have two axis which are the same):

           

          2018-06-08_09h45_37.png

           

          Start changing the colours around, maybe also remove measure names from colour on the marks card.

           

          Ultimately you should get this:

           

          2018-06-08_09h46_06.png

           

          For my example in the above screenshot, I have used min(value) and avg(value). When I sum the values it seems way too high so it doesn't look right. Please let me know how this should be aggregated instead.

           

          I've attached the workbook so that you can take a look.

           

          Thanks,

           

          Mavis

          1 of 1 people found this helpful
          • 2. Re: Invidiual scores and Group average scores in one Chart
            Sean Tobin

            Hello Mavis,

             

            Thank you very much for responding. I started down the data reshaping path but didn't get as far as you did. The workbook you attached is almost exactly what I am looking for. However, the last thing I needed is an average of the entire group broken out by quarter. I added a calculated field to the attached workbook with the changes I made. Thanks again!