In a Pivot Table, how to subtract Sums of Values from a fixed value?

[RogerPenna]

So I am building this spreadsheet to monitor our suppliers.

Each delivery a supplier makes gets grades for several different aspects, like documentation compliance, quality, ponctuality, etc, etc.

The grades for each delivery are summed and multiplied by -1. Because if everything was perfect, grades are 0. Every problem adds a tiny amount.

At the end of the month, the total negative grades are subtracted from a perfect 10 grade.

So, lets say we have two suppliers, JoeMetal and JohnFuel

The table looks like this

JoeMetal : 25/01/2019 : -0,25
JoeMetal : 26/01/2019 : -1,5
JohnFuel : 26/01/2019 : -0,25
JoeMetal : 27/01/2019 : -1
JohnFuel : 05/02/2019 : -0,5
JoeMetal : 10/02/2019 : -2,5
JohnFuel : 15/02/2019 : -0,5

The pivot table I get looks like this

SUPPLIER|JAN |FEB |
JoeMetal |-2,75|-2,50|
JohnFuel |-0,25|-1 |

What I actually want is subtract all those values from 10... so 10-sum(-0,25;-1,5;-1)... 7,25. Or 72,5%, whatever

SUPPLIER|JAN |FEB |
JoeMetal |7,25|7,5 |
JohnFuel |9,75|9 |



How do I proceed to do that? Thanks

 

[sandy666]

try PowerPivot, there you can hide unnecessary column(s)

 

[RogerPenna]

try PowerPivot, there you can hide unnecessary column(s)

I just deleted the Sum of Sum columns. The calculated field I added had a formula that was independent from the other field being there. The formula itself already included the field SUM, which was aggregated by date and supplier, and reduced the base 10 from that negative number.

Thanks.


Trying to get a graph from that right now, having problem with changing the axis of the graph without changing it automatically on the Pivot Table too. But that is another subject I guess, not for this thread.

 

[sandy666]

That is fine you got it to work

have a nice day