Calculating difference between 2 market shares in a Pivot Table

Hello,

I have a Pivot table that contains 2 column of values for 2 different years (2017 and 2016).
The values represent Sales amounts for different companies during the 2 years.
I converted the values to "% of Grand Total" in order to find the market share of each company during each year.
I need now to find the difference between the market shares of the two years for each company, so if a company had a market share of 22% in 2017 and 24% in 2016, then the answer would be -2 (lost 2 points of market share vs the previous year)
Does anyone know if this can be done in Pivot Table?

P.S. I need it to be calculated within the pivot table because the values are dynamic and are linked to slicers that change the values based on specific selections.

I have attached a sample file (Market Share test example) for better visualization.

Thx

is that what you want?

done with DataModel - PowerPivot

Dear Sandy666,

Yes, this is pretty much what I need, but I am not able to do it in the normal Pivot solution.
You mentioned that it is done by DataModel - PowerPivot, so I need to get that installed?

Thx

I think you should install PowerPivot and PowerQuery to get much more possibilities for Excel. These are very powerful and useful tools Both are free add-ins from MS site - for Ex2013 Pro Plus or just upgrade to Excel 2016 where these tools are built-in.

Dear Sandy666,

Thx a lot, this was useful, I am gonna take your advice and download these tools and try to use them, I think they will help me also for future excel projects.

I hope so

If that takes care of your original question, & to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
If you did it already - ignore it.
Thank you.

Dear Shukla,

I tried your formula and although it gives some answers, it doesn't actually give the right answers that I want, I think maybe it is because the actual data sheet is much bigger and contains much more variables than the one I attached above.

I will try to lookup the formula that you suggested online and maybe try to understand it and then adapt it to my data sheet.

In all cases, thx for the suggestion, now I have 2 things to try

Hi Sandy666 I was trying to download this book to see how you did the formula in powerpivot and can't open it. Are you able to help me with the Calculating difference between 2 market shares in a Pivot Table?