Calculating marked shares per company each year in Pivot based on sales figures

Hi

I am working with a dataset consisting of yearly sales figures for different suppliers.
I want to look at marked shares per supplier for each year.
When I Use pivot table I can right-click on the sales numbers and choose "show as % of column sum" and get the numbers i want.

The problem occurs when I use a slicer to look at one or a couple of the companies. The marked share then changes and does no longer show the market share, but the % of sales in that selected group.
How can I fix this problem?

The attached excel-file is just an example, but my problem is illustrated under the "pivot" sheet.

Perfect solution to my problem, thank you!

Originally Posted by JeteMc

Hello Edsberg and Welcome to Excel Forum.
As you are using the 365 version of Excel you could use Power Pivot to do what you want (see Sheet1 in the attached file).
1. Add the table on the Data sheet to the data model.
2. Add the following measure to the data model:

Formula:

Please Login or Register  to view this content.

3. Format the Market Share measure as percentage with two decimal places.
4. In the pivot table put Company in the Rows area, Year in the Columns area and Market Share in the Values area.
Let us know if you have any questions.

Thank you so much for the helpful reply What would the formula be if I didn't have the sales amount, but wanted to calculate market share based on the number of transactions?
The example is illustrated in the attached file.