Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
97 | |
85 | |
70 | |
67 |
User | Count |
---|---|
116 | |
109 | |
94 | |
79 | |
72 |