How to show Percentage of all data in PivotTable (Not % of summed values)

Hi Forum,

Long time no speak!

I have a problem where I am trying to filter the top 80% of results in a PivotTable and have the SUM of the Capital Expenditure show as a by-product.

What i mean by this is that I am not trying to filter the data so that it shows the top ranking 80% of the data, as this filter takes into account the highest capital expenditure fields and takes the top 80% of those.

What i want to achieve is a filter where it shows the top 80% of all results. i.e. 80% out of 100% of all the Capital Expenditure cases are shown and the 20% at the top (top as in top of the page, not top results by value) are filtered out.

E.g:

Date	Title	Capital
2019	AXE	200
2019	CUBE	150
2019	TREX	3750
2019	TYRANT	5900
2019	PIG	301

The data should filter to top 80% of all cases as:

Date	Title	Capital
2019	AXE	200
2019	PIG	301
2019	TREX	3750
2019	TYRANT	5900

Is this does not make sense I will create a mock-up sheet of what I am trying to achieve/convey! Thanks in advance!

Is this does not make sense I will create a mock-up sheet of what I am trying to achieve/convey! Thanks in advance!

That is always a good idea.

And what should be the result in your file.

Since I expect you want € 4.805,10 as result and you have that result already in your file.

Sort the data.

E1 = 80%

E3 =IF(COUNTIF($B$3:$B3,$B3)<=($E$1*COUNTIF($B$3:$B$27,$B3)),"YES","")

After that a pivot table.

See the attached file.

Hey oeldere!

Thanks for that rather easy, elegant solution.
I was hoping it could be done somewhere within the pivot table but this is a good idea. Means I only have to change one value and then update it.

Would there be any way to do it in the PivotTable's current format without modifying the data? If not then I know for next time to create a helper column

I searched for a solution within the pivot table, but was not able to find it.

So, I come with an alternative to solve the question.


Glad I could help.

If you are satisfied with the solution, please mark the question solved.

You can add reputation by clicking on the star * add reputation.