1 Reply Latest reply on Aug 29, 2016 10:59 AM by Benjamin Greene

    Sum of reference lines based on filter selection

    Li Xiang
    Li Xiang Aug 29, 2016 9:29 AM

      Hi tableau masters,

       

      This is similar to a budget (plan v actuals) tableau setup, but I cannot figure out the code or calculation or settings to accomplish this.

       

      The first picture is an easy one. I have a total budget plan of 82. That includes all sub-groups.

      2016-08-29_11-24-13.png

       

      What I want to do is have a specific reference line that changes when I select one or a few of the sub-groups. Each sub-group has its own budget plan.

      2016-08-29_11-25-55.png

       

      Thanks tableau community in advance

      • 58 Views
      • Categories: Actions & Filters, Calculations, Formatting & Display, Queries & Performance, Views & Dashboards
      • Tags:
        • 1. Re: Sum of reference lines based on filter selection
          Benjamin Greene
          Benjamin Greene Aug 29, 2016 10:59 AM (in response to Li Xiang)

          Looking at the pictures you posted, I'm assuming you do not already have a field in your data with the budget values. Is this true?

           

          If so, I think you'll want to code that in using a Budget calculated field like:

           

          IF [Sub-Group]="A"
          THEN 150

          ELSEIF [Sub-Group]="B"

          THEN 160

          ELSEIF [Sub-Group]="C"

          THEN 170

          END

           

          You'll obviously need to extend this logic as far out as necessary to cover all your subgroups, but this will write that total budget value to every single row in the data, based on that row's subgroup. So you can drag this field into your view, set the aggregation to AVG and then right click the axis and select Add Reference Line. Based on your pictures, I think you want Scope to be set to Entire Table, Value to be set to AVG(Budget) and and Label to be None. You can play with these settings (as well as the formatting) until you get something you like. This solution is posted in the attached workbook with some sample data.