Two mistakes you should avoid when calculating Gross Profit Percentage in Pivot Table

I received a question today from one participant in my Hidden Secrets course. She asked:

Why is Material A not reflecting the correct Gross Profit percentage in the Pivot Table even though she has formatted it into %?

When I took a look at the Pivot Table, I immediately saw the 2 mistakes she made.

The first mistake was taking the SUM of Gross Profit.

She had taken the Sum of Gross Profit and Pivot Table added up the Gross Profit number for Material A. And the total is 32.05.

The second mistake was formatting it to %

When she format the number in Pivot Table to %, Pivot Table presents it as 3205%. This is because Pivot Table (in fact Excel in general) take the number and multiply it by 100%. That’s why when she format the number 32.05 as %, it will multiple by 100% and this resulted in 3205%.

So she needed to change the source data to decimals and not multiply it by 100.

To correct show the Average Profit Margin, use Average instead of SUM.

Another problem popped up after the change.

The GP% shown in the Pivot Table for Material B showed 13.81% instead of 13.79%. The two numbers are very close so it is okay?

No, it’s not okay. Even though the 2 numbers are very close, they are not the same.

The one in Pivot Table is showing the average gross profit percentage while the one we are usually expecting is the weighted gross profit margin. To clearly explain the 2, it is best to watch the video below.