1 Reply Latest reply on Feb 22, 2012 5:52 AM by Jonathan Drummey

    Creating Budget vs. Actual report in Tableau

      I am attempting to create a location dashboard for our company that measures actual retail locations added vs. budget. 


      I am pulling the actuals from a table in our data warehouse.  I am attempting to measure actuals this quarter vs. budget.  In the data warehouse table I have a dimension titled "ActiveDt".  Through using a few filters, I get the number.  I get the number in (what could be) a backwards way.  I duplicate a dimension titled status and move it to the measure pane and do a count of status.  The reason I use status is because when a location is made active, they have a status of "S" - therefore I do a count of status' with an active date of this quarter and i get actual installs.  I
      HOWEVER -


      I input budget numbers by BudgetDate in an excel spreadsheet.  I want to have the excel spredsheet numbers displayed, and then bring in the installs from a seperate connection and get one row that says actual installs and another that says budget installs. 


      The problem I'm having is the merge. I go the excel spreadsheet and add the budget numbers, and then switch data sources and add the count of status number and it doesn't put the number as a text as it does the budget number, it overlays the budget numbers with a color.  (see the attached picture)


      I can use "Number of Records" measure in the budget sheet and i get another result, which is closer to what I want, but i can't get the numbers correct.  Its not letting me use the filters form the data warehouse sheet where i would get installs when I'm on the excel sheet (See second screenshot)

      Can anyone please provide guidance!?!?


      Thank you all so much!

        • 1. Re: Creating Budget vs. Actual report in Tableau
          Jonathan Drummey

          I don't feel very clear on the issue here. You say "It's not letting me use the filters from the data warehouse sheet" yet I'm not seeing any filters being applied. The only place you're using the calculated field CNT([Status (copy)]) is on the Color shelf in the first screenshot, and that is working as expected, as far as I can tell - there are only actuals for January and February, so those are the only cells being colored.


          On the second screenshot, you're using Measure Names/Measure Values. You noted that the calculated field is accurate, so try putting the CNT([Status (copy)]) field on the Measure Values shelf, and that might get what you want. Other than that, I'd suggest posting a packaged workbook with some sample data, and with that specify what the actual totals should be for that data.