Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I have an issue with a particular stacked bar chart. Even though I've read several post in regards to this issue I could not solve my problem.
Background: I have several people switching between locations and working. I want to show in one stacked bar chart per person how many days the individual person worked in the different location. This bar chart should be sorted by the switch number (starting with Switch No 1 and then 2, 3).
The following screenshot shows the logic:
Now if I want to visualize this it messes up the sorting:
As you can see e.g. Horst sorting is wrong. His first location was not Loc A, it was Loc C. Same for Helga: First location was Loc B and not as shown here Loc A.
If I use as a legend the Switch No then it works but I cannot show the location (only as a tool tip):
Appreciate your help or any hints.
Thanks
Solved! Go to Solution.
Hey, great that it was helpful!
I tweaked the code a little, redownload the pbix file and have another look. Now the chart looks like this
You will find this little line inside the R script
manualcolors <- c("Loc A" = "#800000", "Loc B" = "blue", "Loc C" = "darkgreen")
Here you can define the colors, the vector "manualcolors" is used further down in the script. Be aware that you can use hexcodes and also constant color names (be aware that you may know more colors by name than ggplot does :-)). I always recommend using hexcodes. Creating a dynamic number of sequential or diverging colors depending on the distinct number of locations in your dataset, is more advanced.
I also use the "geom_text" twice, to place the values inside the segments and also on top (outside) of the bar 🙂
The function aggregate (used to create the dataset for total values) seems to be a little outdated in comparison to the packages from the "tidyverse" (ggplot2 belongs to this group of packages) or in comparison to the usage of the data.table package (from my personal point of view, the fastest data munging, agregating, and other data related operations package available, but also not the easiest one).
Asking for a good tutorial, maybe this will gets you started:
https://www.datacamp.com/courses/data-visualization-with-ggplot2-1
These books are good reads about ggplot2
Regards
Tom
Hey,
unfortunately it's not possible with a default visual, this is due to the fact, that the segements of each bar are ordered the same way and not indivually.
So your 2nd chart does not exactly shows the same as your 1st chart, the 2nd chart shows how long the individuals endured in the 1st location and so on, whereas the the 1st chart should additionally show the sequence of the locations each individual has stayed at.
I guess you have to create your own custom visual either using R or D3.
Regards
Tom
Hey,
here you'll find a little pbix file that contains a R script visual, maybe you'll find this helpful. Of course you can make it look "more Power BI like" but that would be another story, meaning adding some few lines of script.
Here is the plot as screenshot
Hi,
thanks for the quick reply. I tried to open your file but got the following message:
Can't display this visual.
R script error.
Error in library(ggplot2) : there is no package called 'ggplot2'
Execution halted
Details:
Feedback Type:
Frown (Error)
Timestamp:
2017-10-25T09:26:55.6775048Z
Local Time:
2017-10-25T11:26:55.6775048+02:00
Session ID:
e68d626f-99af-454b-b466-14348541a71a
Release:
October, 2017
Product Version:
2.51.4885.701 (PBIDesktop) (x64)
Error Message:
R script error.
Error in library(ggplot2) : there is no package called 'ggplot2'
Execution halted
OS Version:
Microsoft Windows NT 6.1.7601 Service Pack 1 (x64 en-US)
CLR Version:
4.6 or later [Release Number = 393297]
Peak Virtual Memory:
3.75 GB
Private Memory:
273 MB
Peak Working Set:
395 MB
IE Version:
11.0.9600.18792
User ID:
1935631a-285e-438e-a1ab-e8e6c682f9de
Workbook Package Info:
1* - en-US, Query Groups: 0, fastCombine: Disabled, runBackgroundAnalysis: True.
Telemetry Enabled:
True
Model Default Mode:
Import
Snapshot Trace Logs:
C:\Users\PZ0QHT\AppData\Local\Microsoft\Power BI Desktop\FrownSnapShot2054419335.zip
Performance Trace Logs:
C:\Users\PZ0QHT\AppData\Local\Microsoft\Power BI Desktop\PerformanceTraces.zip
Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_EnableReportTheme
PBI_numericSlicerEnabled
PBI_SpanishLinguisticsEnabled
PBI_daxTemplatesEnabled
CustomConnectors
PBI_reportBookmarks
Disabled DirectQuery Options:
PBI_DirectQuery_Unrestricted
Cloud:
GlobalCloud
Activity ID:
null
Time:
Wed Oct 25 2017 11:25:42 GMT+0200 (W. Europe Daylight Time)
Client Error Code:
ServiceErrorToClientError
Error Details:
R script error.
Error in library(ggplot2) : there is no package called 'ggplot2'
Execution halted
Stack Trace:
Microsoft.PowerBI.ExploreServiceCommon.ScriptHandlerException: R script error.
Error in library(ggplot2) : there is no package called 'ggplot2'
Execution halted
---> Microsoft.PowerBI.Radio.RScriptRuntimeException: R script error.
Error in library(ggplot2) : there is no package called 'ggplot2'
Execution halted
at Microsoft.PowerBI.Radio.RScriptWrapper.RunScript(String originalScript, Int32 timeoutMs)
at Microsoft.PowerBI.Client.Windows.R.RScriptHandler.GenerateVisual(ScriptHandlerOptions options)
--- End of inner exception stack trace ---
at Microsoft.PowerBI.Client.Windows.R.RScriptHandler.GenerateVisual(ScriptHandlerOptions options)
at Microsoft.PowerBI.ExploreServiceCommon.ScriptVisualCommandFlow.RunInternal(Stream dataShapeResultStream, QueryBindingDescriptor& bindingDescriptor)
at Microsoft.PowerBI.ExploreServiceCommon.ScriptVisualCommandFlow.Run(Stream dataShapeResultStream, QueryBindingDescriptor& bindingDescriptor)
at Microsoft.PowerBI.ExploreHost.SemanticQuery.ExecuteSemanticQueryFlow.TransformDataShapeResult(QueryCommand transformCommand, SemanticQueryDataShapeCommand command, Stream dataShapeResultStream, QueryBindingDescriptor& bindingDescriptor)
at Microsoft.PowerBI.ExploreHost.SemanticQuery.ExecuteSemanticQueryFlow.ProcessAndWriteDataQuery(IQueryResultDataWriter queryResultDataWriter, DataShapeGenerationContext dsqGenContext, EntityDataModel model, DataQuery query, ServiceErrorStatusCode& serviceErrorStatusCode)
at Microsoft.PowerBI.ExploreHost.SemanticQuery.ExecuteSemanticQueryFlow.ProcessAndWriteSemanticQueryCommands(IQueryResultsWriter queryResultsWriter, ExecuteSemanticQueryRequest request, IConceptualSchema conceptualSchema, EntityDataModel model)
DPI Scale:
125%
Supported Services:
Power BI
Formulas:
section Section1;
shared Table1 = let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s5JLC1W0lHyyU9WcATShkBsqhSrgyrjBKSNgNgcQ8YZSBvD9XjkFxWXIJlmDMboMjDTDA0wpJyhToBqSs1JT0QyzghhEZKME1QPyLhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Full Name" = _t, Location = _t, #"Switch No" = _t, Days = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Full Name", type text}})
in
#"Changed Type";
Hey,
the Power BI contains a R script visual, for this reason you have to install R on your local machine and also install the ggplot2 package that I'm using to create the visual.
Please have a look at this site how to install R on your local machine:
https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-r-visuals/
These are the few lines of R script that are necessary to create the visual:
library(ggplot2); thenames <- colnames(dataset) names(dataset) <- make.names(thenames) p <- ggplot(data = dataset) p <- p + geom_col(aes(x=Full.Name, y=Days, fill = Location, group = -Switch.No)) p <- p + labs(x = thenames[1]) p <- p + coord_flip() p
Hopefully this gets you started
Regards
Tom
Hi Tom,
worked out great!
Just one thing I am still not able to show, is the number auf days within the bars.
Any good tutorial on how to add this and maybe change the colors?
Many thanks.
Hey, great that it was helpful!
I tweaked the code a little, redownload the pbix file and have another look. Now the chart looks like this
You will find this little line inside the R script
manualcolors <- c("Loc A" = "#800000", "Loc B" = "blue", "Loc C" = "darkgreen")
Here you can define the colors, the vector "manualcolors" is used further down in the script. Be aware that you can use hexcodes and also constant color names (be aware that you may know more colors by name than ggplot does :-)). I always recommend using hexcodes. Creating a dynamic number of sequential or diverging colors depending on the distinct number of locations in your dataset, is more advanced.
I also use the "geom_text" twice, to place the values inside the segments and also on top (outside) of the bar 🙂
The function aggregate (used to create the dataset for total values) seems to be a little outdated in comparison to the packages from the "tidyverse" (ggplot2 belongs to this group of packages) or in comparison to the usage of the data.table package (from my personal point of view, the fastest data munging, agregating, and other data related operations package available, but also not the easiest one).
Asking for a good tutorial, maybe this will gets you started:
https://www.datacamp.com/courses/data-visualization-with-ggplot2-1
These books are good reads about ggplot2
Regards
Tom
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |