4 Replies Latest reply on Dec 19, 2016 9:09 PM by ankita vaishnav

    How to compare Bar graphs on different workbooks combined in a single dashboard

    ankita vaishnav

      Hello,

      I have this query. Please help me resolve this.

      I have around 6 measures under Actual leadtime in a workbook using bar graph chart. And I have second workbook under Standard leadtime with simliar 6 measures.

      Now I ahve brought these two workbooks in a dashboard. Now when you compare these two workbooks in a dashboard the alignment of axis is not comparable. lIke if my workbook one is showing actual leadtime ->frozen as  14 and my workbook 2 is  showing standard leadtime->frozen as 9 .The bar space used for 14 and 9 is same .According to this for 9 the bar space should be a bit smaller then 14. How can i achieve this.

       

       

      PLease help me resolve this. As the client should be able to see the dashboard and analyse which is low and which is higher just by seeing the bar graph view

        • 1. Re: How to compare Bar graphs on different workbooks combined in a single dashboard
          Simon Runc

          hi Ankita,

           

          Are you able to post some example data...without seeing exactly how the data is structured it's difficult to give you an exact solution (eg. If you are using measure names/measure values for each bar, it would be a different solution to if the measures Frozen, Production...etc. are dimensions).

           

          If you post some example data/workbook I'm happy to take a look

          • 2. Re: How to compare Bar graphs on different workbooks combined in a single dashboard
            ankita vaishnav

            Hi Simon,

             

            Thank you in advance.

            I have attached the twbx file . here as you see in the dashboard there are two worksheets. When you compare sheet 1 and sheet 2  even though the values in both are different the bar graph occupies the same amount of space.

            For Ex: Sheet 1-Avg. s.Frozen value is higher then Sheet 2 Avg.a.Frozen. But the Bar graph is occupying the same amount of space in the view. Technically Sheet 1-Avg. s.Frozen bar should be longer then Sheet 2 Avg.a.Frozen but it is not.

            Please provide a solution for this.

            • 3. Re: How to compare Bar graphs on different workbooks combined in a single dashboard
              Simon Runc

              hi Ankita,

               

              Thanks for the workbook...

               

              So the easiest way to achieve this (and have both sets of charts in the same worksheet) would be to re-shape the data. So that you'd split out the "a." and "s." into separate rows, so your data would look like this

               

               

              This way you could just use AVG(Frozen), AVG(Others), AVG(Production)...and then just bring the Measure Type (as I've called it) onto the rows.

               

              If this isn't an option, I do have a workaround...which lets you sync the axis over 2 different sheets, using Reference Lines.

               

              The general idea is that I create a field for each of the measures Frozen, Production...etc. which get's the MAX of the "a." and "s." measures. We can then use these (in each sheet) to create a reference line and so force the Axis range to be the same.

               

              So for Frozen...

              [Frozen MAX]

              IIF(AVG([s.Frozen])>=AVG([a.Frozen]),AVG([s.Frozen]),AVG([a.Frozen]))

               

              I then bring this into the detail shelf (in both sheets) for a.Frozen and s.Frozen...I then set up reference lines from this field. We can set up the reference lines to have no label and no line

               

               

              In the attached, I've done this for Frozen and production. For Frozen I've left the reference line visible, so you can see what it does, but have changed line = None for production, so you can see the final effect. You'll need to create this for each of the measures and repeat.

               

              Hope that does the trick, and makes sense...but let me know if not.

              • 4. Re: How to compare Bar graphs on different workbooks combined in a single dashboard
                ankita vaishnav

                Hi Simon,

                 

                So grateful for the workaround. The Max calculation is the one which actually solves my whole issue as I have to show the comparison.I will add the same calculation to other measure fields.

                Thank you again.

                 

                Regards,

                Ankita Vaishnav