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

% of Total Calculation

Colin Clark Sep 1, 2017 4:35 AM

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

John Sobczak Sep 1, 2017 4:49 AM (in response to Colin Clark)

sum([Volume]) / {fixed [month] : sum([Volume]) }
Like Show 0 Likes(0)

Actions
2. Re: % of Total Calculation

Colin Clark Sep 1, 2017 5:26 AM (in response to Colin Clark)
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.

venue data.twbx 46.1 KB
Like Show 0 Likes(0)

Actions
3. Re: % of Total Calculation

Dmitry Pavlov Sep 1, 2017 6:05 AM (in response to Colin Clark)

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

Compute Using: [Venue (Dimension)]
Like Show 0 Likes(0)

Actions
4. Re: % of Total Calculation

Mahfooj Khan Sep 1, 2017 5:53 AM (in response to Colin Clark)

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
Like Show 0 Likes(0)

Actions
5. Re: % of Total Calculation

Zhouyi Zhang Sep 1, 2017 6:43 AM (in response to Colin Clark)

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
Like Show 0 Likes(0)

Actions
6. Re: % of Total Calculation

Colin Clark Sep 1, 2017 10:22 AM (in response to Mahfooj Khan)

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
Like Show 1 Likes(1)

Actions
7. Re: % of Total Calculation

Colin Clark Sep 1, 2017 10:23 AM (in response to Dmitry Pavlov)

Thanks, but not following. What do you mean by:
Compute Using: [Venue (Dimension)]
Like Show 0 Likes(0)

Actions
8. Re: % of Total Calculation

Colin Clark Sep 1, 2017 10:25 AM (in response to Zhouyi Zhang)

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.
Like Show 0 Likes(0)

Actions
9. Re: % of Total Calculation

Colin Clark Sep 1, 2017 10:52 AM (in response to Colin Clark)
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.

Sheet 11.csv.zip 268.1 KB
Like Show 0 Likes(0)

Actions
10. Re: % of Total Calculation

Zhouyi Zhang Sep 2, 2017 1:27 AM (in response to Colin Clark)

Hi, Colin

thanks for your data file.

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

ZZ
Like Show 0 Likes(0)

Actions
11. Re: % of Total Calculation

Colin Clark Sep 2, 2017 5:07 AM (in response to Zhouyi Zhang)
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.

market share 2.twbx 103.5 KB

market share.twbx 472.1 KB
Like Show 0 Likes(0)

Actions
12. Re: % of Total Calculation

Zhouyi Zhang Sep 3, 2017 7:01 AM (in response to Colin Clark)

Hi, Colin

Thanks for your updated workbook.

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
Like Show 0 Likes(0)

Actions