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]

are you sure?

10-sum(-0,25;-1,5;-1) ==>> 12.5 not 7.25
but
10+sum(-0,25;-1,5;-1) ===> 7.25

 

[RogerPenna]

are you sure?

10-sum(-0,25;-1,5;-1) ==>> 12.5 not 7.25
but
10+sum(-0,25;-1,5;-1) ===> 7.25

of course you are right, sorry.

but the fact is that I want to have a monthly base perfect score of 10 and the supplier will have several negative grades along the month. Consider negative in the sense of "quality" of the grade, not in the numerical sense.

So I want to decrease, from that perfect 10, the negative grades. In the numerical sense, yes, SUM the negative grades with the base 10.

Despite the error in the sign, I think the rest of the post made it somewhat clear.

 

[sandy666]

it must be a PivotTable?

 

[RogerPenna]

it must be a PivotTable?

well, I started trying to do it with sumifs... it started getting complicated. While I could sum the negative grades based on supplier, I was having trouble to sum, for that supplier, only grades received in the month of the cell above (like jan/19, feb/19, etc)

 

[sandy666]

How about PowerQuery?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]supplier[/td][td=bgcolor:#5B9BD5]Date[/td][td=bgcolor:#5B9BD5]Value[/td][td][/td][td=bgcolor:#70AD47]supplier[/td][td=bgcolor:#70AD47]January[/td][td=bgcolor:#70AD47]February[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]JoeMetal[/td][td=bgcolor:#DDEBF7]

25/01/2019​

[/td][td=bgcolor:#DDEBF7]

-0.25​

[/td][td][/td][td=bgcolor:#E2EFDA]JoeMetal[/td][td=bgcolor:#E2EFDA]

7.25​

[/td][td=bgcolor:#E2EFDA]

7.5​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]JoeMetal[/td][td]

26/01/2019​

[/td][td]

-1.5​

[/td][td][/td][td]JohnFuel[/td][td]

9.75​

[/td][td]

9​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]JohnFuel[/td][td=bgcolor:#DDEBF7]

26/01/2019​

[/td][td=bgcolor:#DDEBF7]

-0.25​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]JoeMetal[/td][td]

27/01/2019​

[/td][td]

-1​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]JohnFuel[/td][td=bgcolor:#DDEBF7]

05/02/2019​

[/td][td=bgcolor:#DDEBF7]

-0.5​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]JoeMetal[/td][td]

10/02/2019​

[/td][td]

-2.5​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]JohnFuel[/td][td=bgcolor:#DDEBF7]

15/02/2019​

[/td][td=bgcolor:#DDEBF7]

-0.5​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

btw. I used 10+ not 10-

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Extracted Month Name" = Table.TransformColumns(Source, {{"Date", each Date.MonthName(_), type text}}),
    #"Pivoted Column" = Table.Pivot(#"Extracted Month Name", List.Distinct(#"Extracted Month Name"[Date]), "Date", "Value", List.Sum),
    #"Added to Column" = Table.TransformColumns(#"Pivoted Column", {{"January", each _ + 10, type number}}),
    #"Added to Column1" = Table.TransformColumns(#"Added to Column", {{"February", each _ + 10, type number}})
in
    #"Added to Column1"[/SIZE]

 

[RogerPenna]

Looks good, but that is like greek to me. The code is VBA? Does it work in other Excel languages? (portuguese here). I don´t even know where do I access PowerQuery, or if I have it.


Seeing your signature now... Excel 2013 (MS Office Home and Business 2013) Brazilian Portuguese.

 

[RogerPenna]

Ok, I don´t even have PowerQuery in my Excel. I went to the Options, Add-Ins, COM Add-Ins>Go.

Then according to this https://support.office.com/en-us/ar...er-query-2546eaa8-9893-4574-9ef9-afacb9b0b495 I should be able to select MS Power Query for Excel.

Except there is no option to select it. Only COM Add-In available is Power Map

 

[sandy666]

Excel 2010 / 2013 need PowerQuery free add-in
Excel 2016 and above has PowerQuery built-in
this is not vba, but M-language
What is PowerQuery?
and yes it works on different language versions but doesn't change language automatically

btw. this code was created directly from PQ GUI not manually

 

[RogerPenna]

I will try to install it later.

right now I was able to achieve what I wanted by inserting in the pivot table a calculated field that =10+sum (sum being the name of the field with the negative grades). Only problem I have right now is that I have two columns for each month... like... Sum of Sum -0.25 and Sum of Grade 9,75

Have to find a way to hide Sum of Sum (as you can see, my experience with Pivot Table is near nil)