10 Replies Latest reply on Aug 17, 2016 2:03 AM by khalid norat

    Create an Overview Dashboard

    Royal Tran

      Hi all,

      I want to create an overview dashboard with the information as the followings:

      2016-08-09_16h55_24.png

      My overview dashboard should have the following:

      - The layout is the same with one extra column for trend indicator. The trend is based on the variance between current year and last year

      - The color of the number is based on the trend, for example, if the variance is negative, the color is red. Otherwise, it's green.

       

      I have create the followings:

      - 2 measures as display

      - 2 variance, one for each

      - the parameter for choosing between year, when I choose the year, the number above will also change

       

      can you suggest me a way to do so,

      Thanks in advance

       

      Best regards,

      Royal

       

      p/s: I can't attach my workbook since the file is a little heavy

        • 1. Re: Create an Overview Dashboard
          khalid norat

          Create a Parameter with a List of Years.

           

          then you will require calculated fields for

          current year QTY

          Previous Year QTY

          Current Year Sales

          Previous year sales

           

          they should look like

          Current year qty

          if year(date) = parameter then QTY end

          Previous year qty

          if Year(Date = parameter - 1 then QTY

           

          the same for sales

           

          once you have the current and previous year value you will be able to calculate the variance

           

          use the calculated field for current year and the new variance fields you create in your visualisation

           

          let me know if you get stuck

          • 2. Re: Create an Overview Dashboard
            Royal Tran

            Hi Khalid,

            I did create the parameter and measures for CY, PY, Variance (6 measures in total, with 3 for Sales and 3 for Qty) - with your help in another discussion, thanks again for that

             

            What I am trying to create is something like this

            2016-08-09_21h34_29.png

            Can you show me how to set the followings:

            - Create a trend arrow, since these are two different measures, I don't know how to place them in the same table

            - Set the color for the number based on the trend (variance)

             

            Thanks and best regards,

            Royal

            • 3. Re: Create an Overview Dashboard
              khalid norat

              Am I right in assuming the arrows represent the variance

               

              Variance positive then up

              variance negative then down

              • 4. Re: Create an Overview Dashboard
                khalid norat

                Hi Royal,

                 

                Please have a look at this file.

                 

                I have amended what we did previously

                 

                I had a colour field which defined if the variance was positive or negative.

                 

                You can use this field as a colour or use the field as a shape instead

                 

                I understand your current file is abit heavy but if you can create a duplicate and limit the data by using a datasource filter and selecting top N - use a suitable number to be able to test your data efficiently

                or you can use a date filter on the data source just bringing in a couple of years worth of data

                 

                I can help you create the arrow in your workbook

                1 of 1 people found this helpful
                • 5. Re: Create an Overview Dashboard
                  Royal Tran

                  Hi Khalid,

                  So from your workbook, I assume that my overview can only be done by arrange multiple worksheet together, but cannot be completed using a single worksheet.

                  My data source is live connection to SQL Server, I am using Adventure Works 2012 data source.

                  If it cannot be done using a single worksheet, please let me know

                   

                  Thanks for your support, you really helping my out

                  Best regards,

                  Royal

                  • 6. Re: Create an Overview Dashboard
                    Royal Tran

                    Hi Khalid, I think I found a solution for my problem. After all I just need to add some horizontal and vertical containers then I can add arrow based on my data

                     

                    Thank you for your help

                    Best regards,

                    Royal

                    • 7. Re: Create an Overview Dashboard
                      khalid norat

                      That's correct Royal,

                       

                      It is easier to manage in separate sheets and use containers on the dashboard.

                       

                      But I will have another look at this and see if I can visualise this on the same sheet.

                       

                      will get back to you in a short while

                      1 of 1 people found this helpful
                      • 8. Re: Create an Overview Dashboard
                        khalid norat

                        Hey Royal,

                         

                        So it is possible to have the measures and the arrows on the same sheet.

                         

                        Example attached.

                         

                        Let me know if you need further explanation

                        • 9. Re: Create an Overview Dashboard
                          Royal Tran

                          Hi Khalid,

                          Thanks for your sample worksheet, I guess I will go with the multiple worksheet ideas

                          By the way, is there any way for me to preset the color for my value

                          I will go with you worksheet for example:

                          I will use [Colour] Measure as a Color type, but the problem is that I have to change the parameter so that it will display the positive, negative, or zero value; then I will change the color as my wish.

                          But it's a pain since with each measure, I will have to repeat this step

                           

                          Thanks and best regards,

                          Gia Tran

                          • 10. Re: Create an Overview Dashboard
                            khalid norat

                            The Color[Measure] field is a calculated field already calculating +ive -ive and zero

                             

                            to define colours for each of these drag the measure onto a new empty worksheet and onto color  section on marks

                            this will bring up all values relevant to your data you can now change the colour from these which will apply to all sheets

                            1 of 1 people found this helpful