https://preppindata.blogspot.com/2021/01/2021-week-4.html

2021-04.R

library(lubridate)
library(dplyr)
library(tidyr)
library(readxl)
library(purrr)
library(here)
setwd(here("2021-04"))


wb <- "PD 2021 Wk 4 Input.xlsx"
all_sheets <- wb %>%
    excel_sheets() %>%
    set_names()

datasheets <- all_sheets[which(!all_sheets %in% c('Targets'))]

data <-  map_dfr(datasheets,
                 ~ read_excel(wb, sheet = .x),
                 .id = "sheet") %>% 
    rename(Store = sheet)


targets <- read_excel(wb, sheet = 'Targets')

store_data <- data %>% 
    gather('metric','Products_Sold', -c(Store, Date)) %>% 
    separate(.,metric, sep = ' - ', into = c('Customer_Type','Product')) %>% 
    mutate(Quarter = quarter(Date))

out1 <- store_data %>% 
    group_by(Store, Quarter) %>% 
    summarise(`Products Sold` = sum(Products_Sold), .groups = 'keep')

out2 <- out1 %>% 
    left_join(.,targets, by = c('Store','Quarter')) %>% 
    mutate(Variance_to_Target = `Products Sold` - Target) %>%
    group_by(Quarter) %>% 
    arrange(Quarter, desc(Variance_to_Target)) %>% 
    mutate(Store_Rank = row_number()) %>% 
        ungroup()


write.csv(out2,'Store_Rank_by_Quarter.csv')

gistfile1.txt

"","Store","Quarter","Products Sold","Target","Variance_to_Target","Store_Rank"
"1","York",1,499,490,9,1
"2","Birmingham",1,477,475,2,2
"3","Leeds",1,488,490,-2,3
"4","Manchester",1,440,475,-35,4
"5","London",1,425,475,-50,5
"6","York",2,329,300,29,1
"7","Birmingham",2,346,325,21,2
"8","Leeds",2,331,325,6,3
"9","London",2,324,325,-1,4
"10","Manchester",2,288,300,-12,5
"11","Manchester",3,350,300,50,1
"12","Birmingham",3,348,300,48,2
"13","London",3,312,300,12,3
"14","Leeds",3,279,300,-21,4
"15","York",3,273,300,-27,5
"16","London",4,422,400,22,1
"17","York",4,340,330,10,2
"18","Birmingham",4,404,400,4,3
"19","Manchester",4,327,330,-3,4
"20","Leeds",4,349,400,-51,5

Store_Rank_by_Quarter.csv

power bi solution

Ranked Variance = RANKX(ALL(main[Store]),[Variance],,DESC)