1 of 1 people found this helpful
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.
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.
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.