1 Reply Latest reply on Sep 17, 2017 4:25 PM by Jennifer VonHagel

    Sum Of Unit Cost

    Peter Kotzee

      Hi,

       

      I am relatively new to Tableau and can't figure out how to sum an aggregate calculation.

      It feels simple but for the life of me I can't get it to work. Hope someone can assist or just point me in the right direction.

       

      Here goes:

       

      I have 3 processes, each with costs and it's throughput in units. I would like to calculate the unit cost for each process (which I got to work). But I would then like to sum those unit costs to give me a total unit cost per every month.

       

      Attached is the packaged workbook as well as the excel table to assist with the explanation. The problem is the last total unit cost line in the excel table that I can't get to work in Tableau.

       

      Thanks in advance.

      Capture.GIF

      Capture2.GIF

       

      Peter

        • 1. Re: Sum Of Unit Cost
          Jennifer VonHagel

          Hi Peter, it is possible to do, but it seems to me it doesn't give good information; it seems like the unit cost as is is correct. However, this is how you can achieve what you are trying to in Tableau.

           

          it's an order of operations issue. First each measure gets summed, and then they get divided. This carries into the Grand Total: It is dividing the Grand Total Sums 1240/285.

           

           

          We can change that, but we'll have more flexibility in using calculations if we join your data rather than having it blended as it is now.  I'll go over the join real quick in case you aren't familiar with it (hopefully your data sources will allow a join).

           

          I went to Data, New Data Connection:

          And connected to the Units spreadsheet:

          Then, I used the "Add" link in the left panel to bring in the Costs spreadsheet. Using the "Add" link lets you join rather than blend.

          Don't use New Data Source from the Data menu or the database icon. Then you'll have to blend.

          Using the Add, you'll be able to bring Costs into the Data window and set up your join. I used an inner join, but if it's possible for the Units spreadsheet to have Date/Process that isn't in the Costs spreadsheet (or vice versa), and you want to bring the unmatched records in, then you'll need to choose one of the other joins.

          Ok, now I'll set up the worksheet the way you had it before, and add in another formula:

          Unit Costs: Divide Then Sum --- Sum([Costs]/[Units])

          Unit Costs: Sum Then Divide --- Sum([Costs])/Sum([Units])

           

          The [Unit Costs: Divide Then Sum], you have to be careful with calculations like these - if you have other dimensions that bring more granularity to the data, be very careful when you use it that the results you get are what you expect .  It has to divide at the level of granularity you wish it to.

           

          Ok, I hope that helps.

           

          All the best,

          Jennifer