Keyur Parekh 3 Posted April 5, 2019 Share Posted April 5, 2019 Hi Everyone, I have attached excel and spotfire worksheet. It's a data for product category by their order # which is not unique. I want to create a stacked bar that shows correct percentages allocation as excel (Pivot sheet) When I am creating this in spotfire it gives me wrong factor. E.g. OT is 45% which is 47% in excel. How can I achieve this Thank you so much for your time. Thanks, Arpitha Link to comment Share on other sites More sharing options...
Gaia Paolini Posted April 6, 2019 Share Posted April 6, 2019 It is the data that is difficult to plot this way, and I am not sure I understand how Excel is interpreting it. 18+1+1+47+29+9=105% Excel (but displays Grand Total = 100% bottom right) 17+1+1+8+45+28=100% Spotfire The total count of unique orders by category is 503, not 479. 84+7+7+42+224+139 = 503 503-479 = 24 The difference between your calculations in Spotfire and Excel is that some orders appear in multiple categories. There are 24 of these orders. For instance Order #131294 is in CI and SD. Since you are colouring (so splitting the bars) by category, some orders will appear in more than one bar, which takes the total to 503. So Spotfire is not incorrect. I suggest to create a [Categories] calculated column that tells you what really happens with the Order to Category allocation: [Categories] = UniqueConcatenate([Category]) OVER ([Order #]) Then colour by [Categories] in your bar chart. There are 11 of them, not 6 as 5 of them are combined categories. The result is different from both previous results, but hopefully it will give you a clearer picture of what you have. (Hopefully your data is ordered by category so you dont run the risk of having e.g. CI,IL and IL,CI as two different values of [Categories].) Gaia Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now