7 Replies Latest reply on Apr 11, 2012 2:27 AM by jj.jj

# use grand total in calculated field?

Hi All,

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?

regards

Edgar

• ###### 1. Re: use grand total in calculated field?

Hi Edgar,

I wasn't clear about what method you really want to get to the number 3 as a denominator, do you want a count of customers or a count of sales? For this solution, I just used 3, you could create an additional calculated field and stick that in the formula below.

You need to do a nested table calculation with different Compute Usings. I created two calculated fields, one based on the Analysis->Percentage of that you did that I named % of Total Along Channel, and then a second called Channel Shares of % of Total that simply does a WINDOW_SUM([% of Total Along Channel])/3.

When you bring the Channel Shares of % of Total into the view, you then need do the following:

1. Click on the pill, and choose Edit Table Calculation. The Table Calculation dialog appears.

2. Set the Compute using for the Channel Shares of % of Total to be Customer.

3. Click on the Calculated Field drop-down for % of Total Along Channel, and set the Compute Using for that to be Channel.

4. Click OK.

If you want to only show one set of results for all sales channels, then you change the Channel Shares calculation to be the following:

IF FIRST()==0 THEN

WINDOW_SUM([% of Total Along Channel for Customer],0, IIF(FIRST()==0,LAST(),0))/3

END

This uses a couple different techniques to reduce the # of calculations Tableau is performing. Then you can use this new calc (with the same Compute using settings as above) and put the customer field on the Level of Detail.

See the attached workbook for details.

Jonathan

• ###### 2. Re: use grand total in calculated field?

Hi Jonathan,

Thanks a bunch for the reply. I got it all working, so that's grand!

I feel a bit silly though, because I do not manage to create the calculatated field that you are referring tot in the first paragraph. In this case the number of customers equals the number of purchases. In this case the number indeed is 3, but naturally I want to built-in a bit more functionality. I figured I would have to use a calculated field with for instance TOTAL(COUNTD(customer)), but somehow that does not appear to work. I you find the time to help me out on this one I would be very grateful and in the meanwhile I'll try to figure it out myself!

Regards

Edgar

• ###### 3. Re: use grand total in calculated field?

Hi Edgar,

The Microsoft JET engine that Tableau uses for reading Excel, Access and text files does not support COUNTD, so to do that you need to extract the data in Tableau. You can do that by right-clicking on your datasource and choosing Extract Data... from the context menu.

Jonathan

1 of 1 people found this helpful
• ###### 4. Re: use grand total in calculated field?

Hi Jonathan,

I tried making an extract, but still it does not quite work. I created a calculated field reading TOTAL(COUNTD(customer)), but then it counts 2 for sales channel 1 because consumer C had no contact with sales channel 1.

Really sorry to bother you again, but many thanks for your help so far,

best regards

Edgar

• ###### 5. Re: use grand total in calculated field?

Hi,

Although in your table sales values are not wtitten. but i have put some sales value and calculated attached.

you have to put sales channel in colour and sales in angle and then make quick table calculation to percent total and then show mark levels, you will get the result.

Thanks,

Ranjan

Message was edited by: Ranjan Sahoo

• ###### 6. Re: use grand total in calculated field?

Hi Edgar,

The issue with the TOTAL(COUNTD([customer])) not working is the partitioning, by default it's counting the number of customers per sales channel. To get a unique count of customers across all sales channels, you need to create a separate calculation for the TOTAL and then put that field into the Channel Shares calc, I called it "# of Customers" in the attached.. Then, you can click on the green Channel Shares pill in the view to bring up the Edit Table Calculation dialog and for the # of Customers set the Compute Using to customer and sales channel: Does that work for you?

Jonathan

1 of 1 people found this helpful
• ###### 7. Re: use grand total in calculated field?

Thanks for all the help! It works like a charm now!

regards

Edgar