cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
BBCuser Frequent Visitor
Frequent Visitor

Stacked bar chart sorting issues

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:

Table

 

Now if I want to visualize this it messes up the sorting:

 

Stacked Bar Chart 1

 

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):

 

Chart 2

 

Appreciate your help or any hints.

 

Thanks

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Stacked bar chart sorting issues

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

 

R stacked chart ordered segments 2.png

 

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

 

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
6 REPLIES 6
Super User
Super User

Re: Stacked bar chart sorting issues

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

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Super User
Super User

Re: Stacked bar chart sorting issues

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

R stacked chart ordered segments.png

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
BBCuser Frequent Visitor
Frequent Visitor

Re: Stacked bar chart sorting issues

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";

 

Super User
Super User

Re: Stacked bar chart sorting issues

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

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
BBCuser Frequent Visitor
Frequent Visitor

Re: Stacked bar chart sorting issues

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.

Super User
Super User

Re: Stacked bar chart sorting issues

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

 

R stacked chart ordered segments 2.png

 

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

 

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 249 members 2,579 guests
Please welcome our newest community members: