How to create a percentage of the total against pre-applied filters?

Question & Answer
, ,
1

First, I created a pivot table, in which the first column indicates the Top 5 highest values from the “rank()” formula. However, in the third column of my table, I need to indicate the percentage in relation to the total, but when I apply the filter so that the table only shows the Top 5 values, this percentage of the total continues to show the value in relation to the entire total, not only among the Top 5. So, how would it be possible to carry out this calculation?

im1

So in the image above, we see that the percentage in relation to the total is correct

im2

However, which I apply the filter to show only the Top 5 values, the percentage in relation to the total is not correct, as you can see in the image above

2

@pedrohro Are you using Pivot -Tables’ Table calculation? If so, would you be able to share the field-well? If you created a new calculated field for the purpose, will you be able to share the expression for the same?

I was able to achieve your requirement using Pivot Tables Table calculation with Table-down

Please see below.

pivot-table-no-filter
pivot-table-no-filter717×536 43.8 KB

pivot table with filter
pivot table with filter1267×764 73.5 KB

3

Hello Vetri, could you share the step by step you’ve done since creating the ranking, filter and percentage of the total? Because apparently it worked for you but mine still has the wrong percentage.

4

try using the rank() function, and then add a filter on this field after (<=5).

rank( [sum(Sales) DESC], [Industry])

This should essentially ‘hide’ those rows and not really ‘filter’ them out of the underlying query.