7 Replies Latest reply on Aug 2, 2017 4:09 PM by Manuel Velasquez

    Sales per customer by month

    Alex Martino

      I have five customers that started in January (start date)

       

      These customers bought 9 items (9 sales). The average sales per customer is 9/5 or 1.8.

       

      However, these sales occur at different months (sales date). How can I calculate average sales per TOTAL customer set. For example, if there were 4 sales in March. It would be an average of 4/5 sales per customer. Spreadsheet attached.

       

      On the second tab, you will see sales per month and then a row for sales per customer by month. The problem is that it's not computing correctly and I'm not sure why

        • 1. Re: Sales per customer by month
          Angie Connell

          Hi Alex,

          Just to clarify, are you looking for average sales per customer? So not by month?

          • 2. Re: Sales per customer by month
            Alex Martino

            Hello. I calculated average sale per customer as a whole already. I just want to say in each month how does that break out. If there are 3 sales in a month, take that as ratio to the initial customer base. If there are 3 sales in a month, that would be 3 divided by total customer base etc etc. The ratio for all the months sums up to overall ratio.

            • 3. Re: Sales per customer by month
              Manuel Velasquez

              Hi Alex

               

              One way to get the result you're looking is to used a FIXED LOD expression to get the total number of customer in your data set when you have that value, then you can calculate the ratios as need it

               

              Getting TOTAL number of customers in your data source

              1.PNG

               

               

              Then calculating the ratio

              2.PNG

               

              In your view, this is the breakdown per month

               

              3.PNG

               

               

              I attached .txbx file for reference

               

              Let me know if this helps

               

              Manuel Velasquez

              1 of 1 people found this helpful
              • 4. Re: Sales per customer by month
                Alex Martino

                Perfect.

                 

                I tried Fixed (Customer ID) : Sum(Sales)}

                 

                What is the difference?

                • 5. Re: Sales per customer by month
                  Manuel Velasquez

                  The difference is that when you introduce a dimension before the aggregation on a LOD expression you control result no matter what dimension you have in your columns or rows shelf.

                   

                  With {FIXED CustomerID : SUM(Sales)}, pretty much you are telling Tableau : For each customerID return the SUM of sales.

                  (one row per unique customer with the sum of sales)

                   

                  In your case, you need the total number of customers in your data set, so that is what I did not introduce any dimension on the FIXED :  COUNTD(CustomerID), (One row for all the data set with the count distinct of customers )

                   

                  Hope this makes sense!

                   

                  Manuel Velasquez

                  1 of 1 people found this helpful
                  • 6. Re: Sales per customer by month
                    Alex Martino

                    Got it thanks!! One more question, how could I allow that fixed total to vary by a particular date dimensions (and just that dimension) on the rows?

                    • 7. Re: Sales per customer by month
                      Manuel Velasquez

                      Depending on your requirement you could :

                      *Pass a date part on the LOD { FIXED CustomerID, MONTH(date) : AGGREGATION}

                             (One row per each customer and month combination with the aggregation you specified)

                                { FIXED MONTH(date) : AGGREGATION}

                            (One row per each month with the aggregation you decide [count,max,min,etc])

                       

                      * Use a parameter

                      * Nested IF ELSE with LOD combination

                       

                      Hope this make sense

                       

                      Manuel Velasquez