[Excel pivot function to change count to sum](https://i.stack.imgur.com/VjrmD.png)#Using pivottabler package in r

### In Excel pivots, value field settings allow a change from count to Sum. Using code from CBailiss that was posted in this forum on a subset of the inbuilt data set, bhmtrains, this code generates a pivot table but the sum is no different from the count.[pivot generated by this code](https://i.stack.imgur.com/QqXex.png) [pivot in Excel to generate in R](https://i.stack.imgur.com/4Zw8E.png)

```
library(pivottabler)

```

function by CBAILISS
--------------------

```
getPercentageOfAllCategories <- function(pivotCalculator, netFilters, format, baseValues, cell) {
trains <- pivotCalculator$getDataFrame("bhmtrainsSub")
netFilters$setFilterValues(variableName="TrainCategory", type="ALL", values=NULL, action="replace") 
filteredTrains <- pivotCalculator$getFilteredDataFrame(trains, netFilters)
totalTrainsAllCategories <- nrow(filteredTrains)
percentageOfAllCategories <- baseValues$N / totalTrainsAllCategories * 100
value <- list()
value$rawValue <- percentageOfAllCategories
value$formattedValue <- pivotCalculator$formatValue(percentageOfAllCategories, format=format)
return(value)

```

}

sum function that returns count - seeking a fix or a better solution
--------------------------------------------------------------------

```
getSumOfAllCategories <- function(pivotCalculator, netFilters, format, baseValues, cell) {
trains <- pivotCalculator$getDataFrame("bhmtrainsSub")
netFilters$setFilterValues(variableName="TrainCategory", type="ALL", values=NULL, action="replace") 
filteredTrains <- pivotCalculator$getFilteredDataFrame(trains, netFilters)
totalTrainsAllCategories <- nrow(filteredTrains)
SumOfAllCategories <- sum(baseValues$N)
value <- list()
value$rawValue <- SumOfAllCategories
value$formattedValue <- pivotCalculator$formatValue(SumOfAllCategories, format=format)
return(value)

```

}

Create a subset of bhmtrains inbuilt data set
---------------------------------------------

```
bhmtrainsdf <- bhmtrains[c(3:6)]
bhmtrainsSub <- bhmtrainsdf[0:500, ]

```

create and render the pivot
---------------------------

```
    pt <- PivotTable$new()
pt$addData(bhmtrainsSub) 
pt$addColumnDataGroups("PowerType")
pt$addRowDataGroups("TOC")
pt$addRowDataGroups("TrainCategory")
pt$defineCalculation(calculationName="N", summariseExpression="n()")
pt$defineCalculation(calculationName="Percentage", caption="%", format="%.1f %%", basedOn="N", type="function", calculationFunction=getPercentageOfAllCategories)
pt$defineCalculation(calculationName="Sum", caption="SUM", basedOn="N", type="function", calculationFunction=getSumOfAllCategories)
pt$renderPivot()

```