2 Replies Latest reply on Jun 8, 2017 2:18 PM by Claire Douglas

    Budget to Actual blend on measure names?

    Claire Douglas
    Claire Douglas Jun 8, 2017 9:12 AM

      I'm working on a KPI dashboard that has actual numbers split up and totaled, and a trend for them as well.  There are 12 data sources being used to create the dashboard, and there are multiple calculations between the data sources.  I have just received budget information and would like to add that context to the dashboard.  Attached is a packaged workbook mock up of the structure using Superstore data and an Excel budget I made up for Jan and Feb 2016, with a dashboard screen shot below.

       

      I can figure out all of the comparisons from actual to budget as separate calculated fields, but I don't think I can bring them into the rest of the dashboard views without separating all of the views into a separate worksheet for each metric.  Is there a way I could color the total metrics, or regional metrics by the difference to budget?  Or to add a reference line to the trend? Or an indicator for each metric?  I think conceptually this would be a blend on measure names, which I don't think is possible.  Is there another creative way of going about putting the data together?

       

      The 10.2 feature to have a separate legend for each metric seems to get closer, but I don't want to color Sales by the Sales values, I want to color Sales by the Sales Budget % Diff.

       

      Thanks for any thoughts you have!

      Claire

       

        • 1. Re: Budget to Actual blend on measure names?
          Michel Caissie
          Michel Caissie Jun 8, 2017 11:12 AM (in response to Claire Douglas)

          Claire,

           

          Here is one possible approach.

           

          You can create a fake dimension with the measure names in a separate datasource.

              -In Excel, create one column with the measure names,  Copy Cells, in Tableau new Worksheet and paste.

           

          Since your filtering is based on a parameter, you can apply it directly in the calculations.  So in all sources, create a calculation, for example if [Recent Time Period] then [Sales] end.

           

          From the new datasource, create a new worksheet with the dimension [MeasureName] (the fake one) on the Rows.

          And create calculations for your Total and budget values for example if ATTR( [Measure Name] ) = 'Sales' then SUM([Sample - Superstore].[Sales (adj)])  end

           

          You can then play with all values in a single worksheet and create a color calculation like

          case ATTR( [Measure Name] )

          when 'Discount' then ([Discount (Total)] - [Discount (Budget)] )/[Discount (Budget)]

          when 'Sales' then ([Sales (Total) ] - [Sales (Budget)] ) / [Sales (Budget)]

          end

           

          See in the attached...

           

          Michel

          • 2. Re: Budget to Actual blend on measure names?
            Claire Douglas
            Claire Douglas Jun 8, 2017 2:18 PM (in response to Michel Caissie)

            Thank you Michel!

            I'm going to take a deep breath (and make a copy of the workbook), and try to move calculations to be built off of a dummy list of the metrics/measure names.  I'm holding on to the fact that this will likely help formatting of the whole dashboard too- there are separate sheets for information from Google Analytics, and retention is separate from sales, etc.  Getting everything to line up has been less than fun!