15 Replies Latest reply: Jun 29, 2016 5:41 AM by Nicolás Afonso Ferrer

How to create in a table relative % against totals

Hi,

I'm trying to créate a table like the following one, showing an additional column with the % of the line agains the total. A relative figure. How can I build this column?

Thank you very much!

Sales           %

James               10            17%

John                  20            33%

Katherine          30             50%

Total                  60            100%

• Re: How to create in a table relative % against totals

For your second column, use an expression like this:

Sum(Sales) / Sum(TOTAL Sales)

• Re: How to create in a table relative % against totals

I see you liked the replies.

Question is: Did it also answer your question?

If so, could you please flag the Correct Answer and possible Helpful answer?

If not, please tell us with what part of this topic we can still help you .

• Re: How to create in a table relative % against totals

Thank you very much!

that gets a long way, however, i have the table divided by 2015 and 2016 and also i take out Ben.

The total is referring to both 2015 and 2016 and including Ben. I get:

2015                                   2016

Sales           %                  Sales            %

James               10            4%                    10           4%

John                  20            8%                  20         8%

Katherine          30             12%                 30           12%

Total                  60            25%                 60           25%

(BEN has 50%. I filtered him in the Row ítem.)

While i would like to get:

2015                                   2016

Sales           %                  Sales            %

James               10            17%                    10           17%

John                  20            33%                  20         33%

Katherine          30             50%                 30           50%

Total                  60            100%                 60           100%

(And Ben, sales have been taken out of the table.)

Thank you very much again!

• Re: How to create in a table relative % against totals

Try this:

Sum(Sales) / Sum(TOTAL <Year> Sales)

• Re: How to create in a table relative % against totals

Thank you for your answer.

I still have to manage how can I take out Sam of all the figures?

• Re: How to create in a table relative % against totals

Did this work?

• Re: How to create in a table relative % against totals

Yes! The year thing worked perfecty. Thank you. However, the Sam ítem i don't know how to remove it. Is it posible to apply a filter to all bata beign used in the table? As I said, i used the filter in the rows, but when I use the TOTAL Sam is still there!

Thank you one again,

Nico.

• Re: How to create in a table relative % against totals

To remove, you can use set analysis. Stefan has provided one expression, you can use that or this:

Sum({<NameField ={'*'}-{'Ben'} >} Sales) / Sum({<NameField = {'*'}-{'Ben'} >} TOTAL<Year> Sales)

His expression will still allow for selection in NameField, the above will always show all NameField except Ben even if you make selection in NameField.

• Re: How to create in a table relative % against totals

Thakn you very much! this worked perfectly!

Have all a nice weekend!

• Re: How to create in a table relative % against totals

It worked!

Now i'm facing the following issue. Im trying to get out also a year out of the table. It is "date.autocalendar.year". How can I add an AND clausure here? I've tried a lot of options, the tool marks them as correct in terms of format but finally there is no effect.

Thanks again.

Nico.

• Re: How to create in a table relative % against totals

May be this:

Sum({<NameField ={'*'}-{'Ben'}, date.autocalendar.year = {2016}>} Sales) / Sum({<NameField = {'*'}-{'Ben'}, date.autocalendar.year = {2016}>}TOTAL<Year> Sales)

• Re: How to create in a table relative % against totals

Thank you again, it worked.

• Re: How to create in a table relative % against totals

Try

Sum({<NameField -= {'Ben'} >} Sales) / Sum({<NameField -= {'Ben'} >} TOTAL<Year> Sales)

as expression in your table chart with dimensions Year and NameField.

• Re: How to create in a table relative % against totals

Thank you very much!

• Re: How to create in a table relative % against totals

Or you can use the relative option available on the expression tab of the straight table object