4 Replies Latest reply: Sep 3, 2018 4:30 PM by omar bensalem

# Total Percentage in table with dim Limitation

Hello Experts.

I'm facing an issue(see attached jpeg):

I've created a straight table with on dimension

The table has the following Columns: C, A(Simple sum), B(simple Sum) and  A/B which shows the ration between A and B,

Now, I wanted to show the top 10 of column C so I used the Dim limitation and set it on fix 10.

After doing so,  the Total Remained the same(meaning calculating sum for all the products). Changing the Total setting per column from auto to sum fixed the problem except for the percentage. I cant use it on column A/B because it shows a simple Total of percentage and not the percentage of the Total row.

In the example attached, the Total Setting in column A/B is  Auto and show 94.6%. The Sum I need Is 57.4%

Regards,

Motty

• ###### Re: Total Percentage in table with dim Limitation

Youc an achieve what u want using the rank() function.

I'll be creatiung dummy data to show how to do it (and please, next time attach some dummu data)

• ###### Re: Total Percentage in table with dim Limitation

Let assume I have this:

Now I want to show the top 2.

To see the rank of each RowNo based on its sum(Value), we use the Rank() fucntion:

rank(sum({<RowNo>}Value),RowNo)

Now I want to show the top 2 RowNo based on the sum(Value) which means the 2 dimensions with rank <=2 : RownNo 4 and 5

Expression 1 (sum): Sum({<RowNo={"=rank(sum({<RowNo>}Value),RowNo)<=2"}>}Value)

Expression 2 (%) : Sum({<RowNo={"=rank(sum({<RowNo>}Value),RowNo)<=2"}>}Value)/ sum(total {<RowNo>} Value)

Result:

• ###### Re: Total Percentage in table with dim Limitation

Solved Using Calculated dimension

=aggr(if(rank(C)<=10,Product),Product)

• ###### Re: Total Percentage in table with dim Limitation

u're welcome...