Creating a user-defined total with grand_summary_rows()

[CallinOsborn]

I have a table that looks like this:

  category family       amount
  <chr>    <chr>        <chr> 
1 SALES    ONLINE SALES 47    
2 SALES    IN STORE     72    
3 COGS     LABOR        28    
4 COGS     TAXES        35    
5 COGS     WORKERS COMP 24    
6 COGS     BENEFITS     33    
7 EXPENSE  AUTOMOBILE   44    
8 EXPENSE  RENT         12    
9 EXPENSE  TELEPHONE    26    

I am trying to create a gt table from this so I have created this code:

library(tidyverse)
library(gt)

category <- c(rep("SALES",2),
              rep("COGS", 4),
              rep("EXPENSE",3)
)
family <- c("ONLINE SALES","IN STORE","LABOR","TAXES","WORKERS COMP","BENEFITS",
            "AUTOMOBILE", "RENT","TELEPHONE")

amount <- c(47,72,28,35,24,33,44,12,26)


output <- as_tibble(cbind(category,family,amount)) %>% 
    mutate(amount= as.numeric(amount)) %>% 
    gt(rowname_col = 'family',
       groupname_col = 'category') %>% 
    row_group_order(c("SALES","COGS", "EXPENSE")) %>% 
    summary_rows(groups = TRUE,
                 columns = 'amount',
                 fns = list(
                     Total = ~sum(.,na.rm = TRUE)
                 ))
output

How do you get the overall total of SALES-COGS-EXPENSE using the grand_summary_rows() function while all of the amounts are still positive?

[rich_i]

Hi! If I'm understanding this correctly, you want a grand summary of the amount column right? If so, your code is quite close. I used the following:

category <- c(rep("SALES",2),
              rep("COGS", 4),
              rep("EXPENSE",3)
)
family <- c("ONLINE SALES","IN STORE","LABOR","TAXES","WORKERS COMP","BENEFITS",
            "AUTOMOBILE", "RENT","TELEPHONE")

amount <- c(47,72,28,35,24,33,44,12,26)


output <- 
  as_tibble(cbind(category,family,amount)) %>% 
  mutate(amount= as.numeric(amount)) %>% 
  gt(rowname_col = 'family',
     groupname_col = 'category') %>% 
  row_group_order(c("SALES","COGS", "EXPENSE")) %>% 
  grand_summary_rows(
    columns = 'amount',
    fns = list(
      Total = ~sum(.,na.rm = TRUE)
    ))

output

And I get this table:

Does this answer your question?

[CallinOsborn]

This isn't exactly what I am looking for. I want the the grand_summary_rows() to have a function that is:
Total = subtotal(SALES) - subtotal(COGS) - subtotal(EXPENSES)
So in this case Total = -83.

I hope this brings some clarification.

[alessio]

In order to close the thread I share the accepted solution from SO (should it be useful for someone else working with the gt package).

Creating a user-defined total with grand_summary_rows()