From what I understand, this should be possible using table calculations, but it's pretty complex. Would you please post a packaged workbook with your example?
Thank you. Attached.
We are basically trying to split the 2 customers I have shown into profitable and non-profitable in USA. we will sum all their profit and all their cost for each month and if cost is greater then profit then they fall into profitable bucket else, into profitable.
Note that my example maybe rather crude as I have 1 record per customer each month, but assume there are more. We first need to sum all profit and all cost per month before figuring out which bucket the customer will fall into.
tb_example.twb.zip 3.9 KB
It looks like you posted an unpackaged workbook and no data source.
This is a resource I sometimes share that explains the difference.
Just be sure to save as Packaged workbook, or make sure you include both the workbook and datasource in the zip file. Otherwise, we still can't get to your data!
Now that I'm seeing the data, let me try to rephrase what you're asking:
You want to split the total revenue and cost into 2 groups based on whether the customer associated with the revenue and cost was profitable (rev > cost) or unprofitable (rev <= cost).
Since your data seems to exists at customer-month level (although there are 2 customer 6-Feb 2012 records) I assume that a customer can be profitable in one month and unprofitable in another.
If this is the case, then all you needed was to create a calculated field to assess whether each customer-month was profitable or unprofitable:
IF [revenue] > [cost]
tb_example.twbx.zip 12.4 KB
Thanks for your response
The data is on transaction level, hence a customer can appear more then once in a given month. Therefore, my earlier point indicates that we first need to sum rofit and sum cost in a given month per customer and then group into profitable/unprofitable bucket. So customer can be profitable this month and unprofitable next month.
Based on your experience, is it even doable in Tableau?
I've calculated a net profit field (revenue-cost), and when tableau visualises this (as sum(net profit)), this should return the net profit on a per customer basis. I've shifted the 'customer' variable under Dimensions. Is something like sheet 2 what you are looking for?
Edited to add:
Created a new variable "Net Profit":
if sum([net profit]) >0 then 'Profitable'
elseif sum([net profit])<=0 then 'Unprofitable' end
tb_example.twbx.zip 17.5 KB