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

# Sales per customer by month

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

Hi Alex,

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

• ###### 2. Re: Sales per customer by month

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

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

Then calculating the ratio

In your view, this is the breakdown per month

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

Perfect.

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

What is the difference?

• ###### 5. Re: Sales per customer by month

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

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

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