2 Replies Latest reply on Apr 16, 2018 4:13 PM by Isis Ibarra

# Percent of Total and Total Calculations

Hello Tableau Community!

I've been working on a dashboard that will track down the weekly market share of some companies.

• Company Name ID: Due to confidentiality issues, I've just summited the company's id, rather than the actual name.
• Consecutive Order ID
• Date: The date when the order was placed.

I need to know the amount of orders placed during the week (# Orders) and the % of the market share. As for now, I've calculated the # Orders with the following formula: ZN(AVG([Consecutive Order Id])) - LOOKUP(ZN(AVG([Consecutive Order Id])), 1), but I'm missing the % of the market share and the total from # Orders . I've tried the following for the % of the market share:

1. SUM([# Orders])/TOTAL(SUM([# Orders])) --> Argument to SUM (an aggregate function) is already an aggregation, and cannot be further aggregated.
2. [# Orders]/TOTAL([# Orders]) --> The input to TOTAL() cannot include a Table Calculation function.
3. And I also tried to do a table calculation using % of Total and/or Running_Sum without success.
4. And finally, for the totals I tried the Analysis > Totals > Show Row Grand Totals but it isn't giving me the expected sum per row.

Any ideas on how to get this numbers?

I've attached the workbook so it will be easier for you to follow what I'm explaining.

Sample Expected Results

Date13
4
6
Total
April 16th, 2018     # Orders11,17332987529112,668
April 16th, 2018    % Market Share88.2%2.6%6.9%2.3%100%
April 6th, 2018     # Orders13,57239198735915,309
April 6th, 2018     % Market Share88.6%2.6%6.4%2.3%100%

Isis Ibarra

• ###### 1. Re: Percent of Total and Total Calculations

Hello,

I do not have right version to open the dashboard attached. I will answer from what I understood from your question.

I guess your calculation of # Orders is complicating your overall situation.

Considering your data is like this

You can create count distinct as #Orders instead of the function you are using.

To get market share , now use any of the options you mentioned in the question, you will be able to create a field which gives you market share. Only thing, you need to make sure your table calculation is table(across) since company IDs are placed across the table.

Hope this helps

• ###### 2. Re: Percent of Total and Total Calculations

Jeevan, thanks for taking the time to answer my question.

You got it almost right! The only issue is that I can't do a COUNTD([Order ID]) since I have only one consecutive order id per week, like the image below:

So, I need to first do the calculation to obtain the number of orders placed in a week (# Orders) and then obtain the % Market Share :/

Any ideas?

Thanks,

Isis