Stacked bar not calculating correct percentage as excel

[Keyur Parekh 3]

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

[Gaia Paolini]

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