5 Replies Latest reply on Dec 28, 2017 10:28 AM by Hari Ankem

    Data Transition Excel-->Tableau

    Daniel Livsey

      I've got a very large Excel data set that I'm trying to move over to Tableau, and have run into issues.  I've been reading article after article, and can't seem to get it to work right.  Essentially I'm trying to replace the 'countifs' and 'sumifs' functions in Excel.  They work very well and I have no issues, but as I'm trying to generate dashboards, etc, this would mean making several tableau workbooks each for 1 sheet.  If I can straighten this out, I'll be able to input just my data set and run with one workbook. 

       

      Here is an example of my data:

       

      JobCategoryProductiveNon-ProductiveEfficiency
      A15271%
      B22340%
      C26186%
      D180100%
      E110283%

       

      Efficiency is calculated as  (Productive ) / (Productive + non-productive).

       

      I have gotten this part done fine and have been using tableau for this for quite some time.  However, I also need to generate the data for each of the Categories, as well as for each of the Jobs.  I've been using a table like the following:

       

      categoryJobsProductiveNon-ProductiveEfficiency
      1323485%
      228467%

       

       

      I use the COUNTIFS() for the Jobs count in this table and SUMIFS() for the productive and non-productive, and the same equation as above for the Efficiency.

       

      Each time I try to replicate this in Tableau, it just sums the individual values, and gives me a result greater than 100%.

       

      Thanks in advance for any help you can provide!