Forum Discussion

BBCuser's avatar
BBCuser
New Member
8 years ago
Solved

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 switch...
  • TomMartens's avatar
    TomMartens
    8 years ago

    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

     

TomMartens's avatar
TomMartens
Icon for Super User rankSuper User
8 years ago

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

BBCuser's avatar
BBCuser
New Member
8 years ago

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

 

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    8 years ago

    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

    • BBCuser's avatar
      BBCuser
      New Member
      8 years ago

      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.

      • TomMartens's avatar
        TomMartens
        Icon for Super User rankSuper User
        8 years ago

        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