3 Replies Latest reply on Oct 5, 2018 2:37 PM by Joe Oppelt

    Dashboard Indicator Lights with Weighted Targets




      I would like to create a dashboard showing a series of weighted targets at a team level that roll up to an top level view (Figure 1) through a series of indicator lights (whether targets are met).


      Each team (Figure 3) should have a set independent/weighted targets (Figure 2) that are blended and that roll up to an overall target (top level view).


      Attached is an Excel workbook of example of what I am trying to accomplish in Tableau.


      Any help is greatly appreciated.




        • 1. Re: Dashboard Indicator Lights with Weighted Targets
          Joe Oppelt

          You're asking a big request here!


          Which part of the design are you struggling with? 


          Creating those reds and greens are pretty straightforward.  Set up individual parameters for the various targets, and then create a calc for each metric that triggers from the parameters:



          METRIC1 CALC


          If ATTR([Team]) = 1 then

            if SUM([whatever measure])>= [Team 1 Target for Metric 1] then "Green" else "Red" END

          ELSEIF ATTR([Team]) = 2 then

          (same for Team 2 Target for Metric 1] ...)

          ELSEIF ... 3 ...




          The next trick will be to roll up all the greens and reds for each metric.  That will use a table calc that will run along the Team dimension:


          WINDOW_SUM( IF ATTR([METRIC1 CALC]) = "Red" then 1 else 0 END )


          And if the value of that calc > 0 then you have at least 1 "Red", so the overall light at the top for Metric1 will be Red.  (This will probably be displayed as a separate sheet.)


          I don't get the extra things at the top (94%, 6.5%...)  Are they just averages of the specific metric targets?  If so, that would be a separate sheet.  (Or 3 sheets!)

          • 2. Re: Dashboard Indicator Lights with Weighted Targets

            Hi Joe,


            Thanks for your answer. Essentially, I am struggling with Figure 1 (Top Level Target Metrics).


            I am currently using a similar format you suggested (If any of the teams are Red, then the Metric 1/Metric 2/ Metric 3 is Red). There wasn't a problem setting targets at a team level.



            However, what I would like to accomplish is the following:


            I would like OVERALL targets set on top 94%, 6.5%... (Figure 1) for all the teams within that metric.


            I would like to  calculate the average of those different team metrics for an overall target (collectively) to compare against an overall Target.


            I.e. All the Teams under Metric 1 Targets (Figure 2) should be averaged and compared with an Overall Target that is independently set (Figure 1). If this sum collectively reaches this target (i.e. 94%, 6.5%) then it would be GREEN, if not, then it would be RED.







            • 3. Re: Dashboard Indicator Lights with Weighted Targets
              Joe Oppelt

              That will take table calcs, and the details of what you need to do will depend on your application.


              Can you upload a sample workbook?