How to create a pipeline chart which breaks counts by daily shifts

[ptepichin]

I am trying to create a visualization to track samples that are tested in a laboratory. I'm trying to track how many samples are received, tested and results are sent to the customers per day. The part i'm currently stuck is how to break it down by 3 separate 8 hour shifts. Can someone assist me in how to go about doing this?

[TomMartens · Accepted solution]

Hey @ptepichin ,

unfortunately this type of chart is not supported by the default visuals of Power BI.

What makes this special is the simultaneous clustering (the bars) and the stacking (the segments that form the bar).

Even if it seems simple it's not.

Years ago I created this r script, to achieve exactly this:

Please be aware that the above chart can be themed to make it appearance more "modern" 🙂

And this is the R code, as I already mentioned it's old, today it would only use the data.table package for all the data shaping because of it's unsurpassed speed:

library(data.table)
library(ggplot2)
library(reshape2)

# ###########################################################################################################
# a sample data set from here: http://stackoverflow.com/questions/25690208/layered-axes-in-ggplot
set.seed(1234)
data <- data.frame(
animal = sample(c('bear','tiger','lion'), 50, replace=T),
color = sample(c('black','brown','orange'), 50, replace=T),
period = sample(c('first','second','third'), 50, replace=T),
value = sample(1:100, 50, replace=T))
dt <- as.data.table(data)
# ##########################################################################################################


# ##########################################################################################################
# another question from here: http://stackoverflow.com/questions/25698229/stackeddodged-beside-barplot-in-ggplot
# data1  = cbind(c(1,1.25),c(1.2,1.5),c(.75,1.2))
# data2  = cbind(c(1.3,1.5),c(1,1.25),c(1.25,.75))    
# dd1 = data.frame(data1)
# dd1$id = 'first'
# 
# dd2 = data.frame(data2)
# dd2$id = 'second'
# dd = rbind(dd1, dd2)
# dd
# 
# dd$row = c(1,2,1,2)
# dt<- melt(dd, id=c('id','row'))



# # parameters
# # the first dataset
groups <- c("period", "animal", "color")
thevalue <- c("value")
variable.inner <- c("period")
variable.outer <- c("animal")
variable.fill <- c("color")

# # the second dataset
# groups <- c("variable", "id", "row")
# thevalue <- c("value")
# variable.inner <- "id"
# variable.outer <- "variable"

# grouping the data.table dt
dt.grouped <- dt[,lapply(.SD, sum), by = groups, .SDcols = thevalue]

# the inner group
# # the second dataset
xaxis.inner.member <- unique(dt.grouped[,get(variable.inner)])
xaxis.inner.count <- length(unique(xaxis.inner.member))
xaxis.inner.id <- seq(1:xaxis.inner.count)
setkeyv(dt.grouped, variable.inner)
dt.grouped <- dt.grouped[J(xaxis.inner.member, inner.id = xaxis.inner.id)]
str(dt.grouped)

# the outer group
xaxis.outer.member <- unique(dt.grouped[,get(variable.outer)])
xaxis.outer.count <- length(unique(xaxis.outer.member))
xaxis.outer.id <- seq(1:xaxis.outer.count)
setkeyv(dt.grouped, variable.outer)
dt.grouped <- dt.grouped[J(xaxis.outer.member, outer.id = xaxis.outer.id)]

# independent from sample datasets
# charting parameters
xaxis.outer.width <- 0.9
xaxis.inner.width <- (xaxis.outer.width / xaxis.inner.count)
xaxis.inner.width.adjust <- 0.01 / 2

dt.ordered <- dt.grouped[order(outer.id,inner.id, get(variable.fill)),]
dt.ordered[,value.**bleep** := cumsum(value), by = list(get(variable.outer), get(variable.inner))]
dt.ordered[,xmin := (outer.id - xaxis.outer.width / 2) + xaxis.inner.width * (inner.id - 1) + xaxis.inner.width.adjust] 
dt.ordered[,xmax := (outer.id - xaxis.outer.width / 2) + xaxis.inner.width * inner.id - xaxis.inner.width.adjust]
dt.ordered[,ymin := value.**bleep** - value]
dt.ordered[,ymax := value.**bleep**]
dt.ordered[,x := get(variable.outer)]
dt.ordered[,fill := get(variable.fill)]


# second dataset
dt.text <- data.table(
  outer = rep(xaxis.outer.member, each = xaxis.inner.count)
  ,inner = rep(xaxis.inner.member, times = xaxis.outer.count)
)
setnames(dt.text, c(variable.outer, variable.inner))


setkeyv(dt.text, variable.inner)
dt.text <- dt.text[J(xaxis.inner.member,inner.id = xaxis.inner.id),]
setkeyv(dt.text, variable.outer)
dt.text <- dt.text[J(xaxis.outer.member,outer.id = xaxis.outer.id),]
dt.text[, xaxis.inner.label := get(variable.inner)]
dt.text[, xaxis.inner.label.x := (outer.id - xaxis.outer.width / 2) + xaxis.inner.width * inner.id - (xaxis.inner.width / 2) ]




# the plotting starts here
p <- ggplot()
p <- p + geom_rect(data = dt.ordered,
aes(
,x = x
,xmin = xmin
,xmax = xmax 
,ymin = ymin
,ymax = ymax
,fill = fill)
)

# adding the values as labels
p <- p + geom_text(data = dt.ordered,
aes(
label = value
,x = (outer.id - xaxis.outer.width / 2) + xaxis.inner.width * inner.id - (xaxis.inner.width / 2)
,y = value.**bleep**
)
,colour = "black"
,vjust = 1.5               
)

# adding the labels for the inner xaxis
p <- p + geom_text(data = dt.text,
aes(
label = xaxis.inner.label
,x = xaxis.inner.label.x
,y = 0
)
,colour = "darkgrey"
,vjust = 1.5
    
)

p

Here you will find an aricle how to create r visuals inside Power BI: https://docs.microsoft.com/en-us/power-bi/visuals/service-r-visuals

Hopefully this provides some ideas how to tackle your challenge.

Regards,

Tom