3 Replies Latest reply on Dec 21, 2014 7:00 PM by Bruce Segal

    Stacked bar chart filtered by percentile - almost there but numbers don't reconcile

    Alex Martell



      I'm trying to create a stacked bar chart adding up to 100%, filtered by percentiles.  I'm almost there but could really use some help because after several attempts I can't get basic numbers to reconcile.


      I have attached the Tableau file and an excel cross check to make it clear where the discrepancy occurs and hopefully make it easy for wiser eyes to let me know where I'm going wrong.


      The components of my stacked chart are categories of a measure I call "Actions".




      I sliced the "Actions" for those actions which were performed by users in the top 5% of another measure I call "Sessions".  I used Jim Wahl 's helpful post on percentiles to arrive at these charts.


      The charts are exactly the charts that I need, but unfortunately they do not reconcile with a quick manual check of the underlying numbers.


      For instance, if you look at the underlying data (see chart below) and divide View / Total actions for Month 1, you should get 75.2%




      But in the percentage breakdown chart, the chart shows 76.5%, a significant difference.




      I copied the underlying data from this chart into excel (attached) and ran my own calculations.  I get 75.2% also from this spreadsheet, so still don't know why Tableau is getting 76.5%.


      Any help greatly appreciated, and please let me know if anything's not clear.  Cheers!

        • 1. Re: Stacked bar chart filtered by percentile - almost there but numbers don't reconcile
          Bruce Segal



          I can diagnose the problem but can't tell if this solution will work for you b/c I don't fully understand your data structure or your measures; in particular the measures that are what I call aggregated measures E.g. "Scroll in Top 5% of Sessions."


          I think the problem is a combination of an aggregated measure issue (your (top 5% of sessions, view measures), plus your custom pctage calcs, plus a data structure issue, pluse trying to do this as you would in .xls. It's our round .xls heads that have to fit into the square tableau hole.


          I say this b/c when I add the sum of scrolls and views to your brown and green graph, I can see that Tableau is correctly calculating the percentage based on the underlying numbers in the works sheet. 35/149 = 0.2349. Your custom calculation in the blue graph is using different numbers, the ones you wrote out 26/105= 0.24xx.


          See 1st image below.



          Part of the problem is the shape of your data. You have a column named Total Actions in the .xls that is the sum of two other columns in the .xls, the Scroll and View columns. As a result you run into issues creating calculations with data in this type of "wide" shape. I wish I had a clearer way to articulate it.


          So, one way to do this is to reshape your data using the free Tableau Data Shaper so you eliminate the column Total Actions (or keep it just in case it's useful), and then create a new Dimension Column Called "Action Type" which creates two rows of data for each current row of data. One row for "Views" and 2d row for "Scroll." Then you name the new Measure "Action Count." Then when you sum the Action Count measure in Tableau it gives you the total of Views and Scrolls. Then you use the new dimension on the color shelf and that will segment your Actions into Views and Scrolls.


          See the attached .xls using the reshaped the data.


          Note that when you reshape the data this way to count users accurately you can't use the sum of the number of records. You have to use the CountD of the UserId's. Also note that to get to the top 5% of Users. I added a filter for UserId and then selected include all, and then selected the condition tab and said to include only UserIds where the Total sessions >= 6. You're .xls shows you calculated 6.4 sessions as the 95% percentile for each row of data.



          1 of 1 people found this helpful
          • 2. Re: Stacked bar chart filtered by percentile - almost there but numbers don't reconcile
            Alex Martell

            Huge thanks Bruce - I think you're onto something re the shape of the data.  My main concern is whether whether I'll be able to preserve the "Months" dimension if I reshape as you suggest - but there's only one way to find out .  Will attempt this and revert with whether it worked.



            • 3. Re: Stacked bar chart filtered by percentile - almost there but numbers don't reconcile
              Bruce Segal

              You're more than welcome Alex. Just passing on similar wall-busting or hurdle-jumping help others I. The Tableau community have helped me.


              Let me know if you encounter any issues when you add in the month dimension.