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

 

  • 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

     

6 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    8 years ago

    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

    • TomMartens's avatar
      TomMartens
      Super 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";