4 Replies Latest reply on Aug 9, 2016 1:28 PM by stephen.ta.0

    Sum of Order IDs with a distinct count and bins

    stephen.ta.0

      New to this forum and had some questions on Tableau...

       

      I have data where Order IDs are duplicated among several rows due to multiple item types being ordered under that Order ID.

       

      I want to sum the quantity of order ID and then create bins by cost range ex: "$1 - $3" and then take a distinct count of how many order Ids would fall in that range.  My end goal was to put this in stacked horizontal bar chart where the row would be the different bin sizes (I figured out the binning part) and the CNT of OrderId would be the column.  I would then shade each bar to see the breakdown of item type. 

       

      To be less confusing the part I am having an issue with is that I need to sum the Order IDs but retain the ability to see the item type and want to the the distinct count of Order ID that fall within a range.

       

      Thanks!                                                                                                                                                                                          

       

         

      Order IDItem TypeCost
      123A$1.00
      123B$2.00
      124A$3.00
      124B$5.00
      125A$9.00
      125B$6.00
      125C$7.00
      126A$2.00
      126B$3.00
      127C$2.00
      127A$5.00
      128C$1.00
      128B$2.00
      129A$3.00
      129B$6.00
      130A$10.00
      130B$11.00
        • 1. Re: Sum of Order IDs with a distinct count and bins
          Benjamin Greene

          Hey Stephen. If I am understanding you correctly, you can achieve this result by dragging the Cost Bin field to the Rows shelf and the Item Type field to Color. Then right-click drag Order ID to Columns and select CNTD(Order ID). Let me know if this is what you were looking for.

          • 2. Re: Sum of Order IDs with a distinct count and bins
            stephen.ta.0

            Hi Ben,

             

            Sorry my explanations are not very clear, with your method it is still binning by each row in the data and not summing the cost per order.

             

            Is there a way when binning that I can sum the Order ID in the bin?  Maybe that is what I am doing wrong?

             

            Thanks

            • 3. Re: Sum of Order IDs with a distinct count and bins
              Benjamin Greene

              OK Stephen, I think I understand your issue now. I have attached a packaged workbook with what I believe to be the solution you are looking for. However, I should specify, this solution requires the use of Level of Detail expressions, which are only available on Tableau 9 and later. So you will want to ensure you are running an updated version of Tableau.

               

              1. I created Total Order Cost as {FIXED [Order ID] : SUM([Cost])} and edited Cost Bin to use this field instead. This is dragged to Rows.

              2. I created Count of Types Within Order as {FIXED [Order ID] : COUNTD([Item Type])} and Count of Distinct Orders as (1/[Count of Types Within Order]). This is dragged to Columns.

              3. I dragged Order ID to Label, Item Type to Color and Total Order Cost and Cost to Detail. I also made sure Order ID is above Item Type in the Marks shelf.

              4. I played with some of the number formatting and the Tooltip to make everything make a little more sense.

               

              Let me know if this is what you were looking for.

               

              Test.JPG

              1 of 1 people found this helpful
              • 4. Re: Sum of Order IDs with a distinct count and bins
                stephen.ta.0

                Hi Benjamin,

                 

                Thanks this is exactly what I was looking for.   Appreciate the explanation and the help!