12 Replies Latest reply on Sep 3, 2017 7:01 AM by Zhouyi Zhang

# % of Total Calculation

How do I create a calculated field to calculate the "exchange % of total volume" based on example below. I don't want to use the quick table "percentage of total" calc.

For example, in Jan, Exchange 1 % of total would be 27.7% [5/(5+6+7)].

Exchange Volume Over Time

 Venue (Dimension) Jan Feb Mar Exchange 1 5 5 5 Exchange 2 6 6 6 Exchange 3 7 7 7

Thank you, Colin

• ###### 1. Re: % of Total Calculation

sum([Volume]) / {fixed  [month] : sum([Volume]) }

• ###### 2. Re: % of Total Calculation

Thanks, getting an error saying can't mix aggregate and non-aggregate arguments. Attached is the workbook with the calculated field called "Volume %". Much appreciated.

• ###### 3. Re: % of Total Calculation

SUM( [Jan] ) / TOTAL( SUM( [Jan] ) )

Compute Using: [Venue (Dimension)]

• ###### 4. Re: % of Total Calculation

Hi Colin,

Getting this error while opening the workbook

As per my understanding you're trying to calculated % of Total. If so then you can try something like this

SUM([Volume]) / TOTAL(SUM([Volume]))

Compute data partitioning based on your requirement.

Mahfooj

• ###### 5. Re: % of Total Calculation

Hi, Colin

A small fix to John's calculation as below should fix your "can't mix aggregate and non-aggregate arguments" issue

sum([Volume]) / sum({fixed  [month] : sum([Volume]) })

ZZ

• ###### 6. Re: % of Total Calculation

Thanks, but this works just like quick table calc % of Total, so if I remove an exchange from the view it recomputes the number. I want the percentage to stay fixed.

1 of 1 people found this helpful
• ###### 7. Re: % of Total Calculation

Thanks, but not following. What do you mean by:

Compute Using: [Venue (Dimension)]

• ###### 8. Re: % of Total Calculation

The calc was valid, but data not correct. Within a given day the volume is sliced into 10 minute increments. Wonder if that is part of the issue.

• ###### 9. Re: % of Total Calculation

Attached is the file with four columns: Trade day, exchange, volume, start time. Start time represents a 10 minute period during a given day. I'm trying to create an exchange market share calculated field (exchange volume/total volume) over time that is fixed. I can do this using a quick table calc "% of total", but I would like to do it using a calculated field where I don't have to show the other exchanges as well. Appreciate your help.

• ###### 10. Re: % of Total Calculation

Hi, Colin

Could you provide us a workbook and show us how you use table calculation with this data file?

ZZ

• ###### 11. Re: % of Total Calculation

Thank Zhouyi - Good news is I retried your calc in a separate workbook and it worked (see attached 'market share' book). Unfortunately the same calc is not working in my main workbook, which has more data fields (see attached 'market share 2' book).

Separate but related, in 'market share' book, I tried you use your calculation for an intraday market share version, but that did not work.

Any insight is appreciated.

• ###### 12. Re: % of Total Calculation

Hi, Colin

I found connection to data source issue with your market share 2.twbx as below

To your question regarding market share workbook, you need a little change by adding the file date to the calculation as shown below

Hope this could help

If you could fix the connection issue I mentioned above, and repost the workbook, I will have a look as well.

ZZ