I have a question about how to use grand total in or as a calculated field (or any other solution to the problem I am facing).
The case is as follows, I am looking at 3 customers (ABC) that come in through 4 different sales channels (1,2, 3 and 4). What I want to calculate is the percentage of attribution that each sales channel gave to the total amount of sales.
The data is organized so that I know that all 3 customers have bought my product and before buying my product they have had contact with one or more of the sales channels one or several times. In the attached XLS file "attribution" I have shown the data the way it is organized now. So, in the first column is the customer ID, in the second column the sales channel that the customer has been in touch with and in the third column it reads a '1' for each event.
The first step is that I want to know what the total numbers are, which Tableau can easily do. I drag customer ID into the rows bar and sales channel into the colums bar. If I than drag the "contact" measure in the table I see what is shown in file "Tableau1".
Now, what I want to now is to what percentage each sales channel contributed to each sale, so I want to know for each customer what the percentage of his contact with each sales channel is. I use pull down 'Analysis' and then 'Percentage of' and then 'Row in Pane' which gives me the view shown in the file 'Tableau2'
The final step of what I want is something I can not figure out how to do in Tableau. For example, for sales channel 1, I want Tableau to take the sum of percentages for each channel(50% + 30% = 80%) and divide that by 3 (which is not only the total amount of users, but also the total number of sales, because I know that each user has done one purchase). The answer should be 26,7%. For sales channel 2,3 and 4 these percentages should repectively be 15%, 24% and 33%.
I can not figure out how to accomplish this in Tableau, because no matter what I try, it takes the weighted average of all sales channel contacts, So for sales channel 1, this gives 53/111 = 47,7%. 53 is the total number of contact moments for customer 1 and 111 is total number of contact moments for all users over all sales channels.
Can anyone help me on how to make clear to Tableau how to calculate the percentages per sales channel?
Thanks a lot in advance,