Preventing totals for text expresions on Pivot tables

[#1]

have a pivot table which has products with their planned source and destination with the associated costs....

I have a 2nd table which has other ' sourcing options' with their associated costs. I calculate the sources that would be the least expensive ( Source 1, Source 2, Source 3) using an expression but the end result is a name of a location (i.e florida, colorado, california, etc) but in my pivot table when everything is aggregated up to the product level, it shows the "Total" for the  Source 1 as Florida, which really makes no sense.

I want it to show the  Source 1, 2, 3 only when I've expanded from Product to Destination.

Any idea on how to go about this?

[JonnyPoole]

Might need to see a sample for this one.

[#3]

I'm trying to create a sample for this. In my sample where I created the sample data, it is working exactly how I want it to...

In my actual application, where you see 'option1,2,3' you would see a total of 1B, 2B, 3B when you have everything collapsed looking at totals for each week...

do you think in my actual application, it is thinking the source locations are numeric values?

[JonnyPoole]

So the sample works the way you want , but there is an issue  when everything is collapsed (that is not in the actual QVW)

.

But the actual QVW does not work.

Does your sample tackle the solution differently than the actual qvw and you now want me to help resolve the collapsing issue ? 

Not 100% sure what issue to solve ?

[#5]

My actual QVW is designed the same way as my sample QVW. My sample QVW works like how I want it to.

However, my actual QVW is giving me totals for text expressions when the pivot table is collapsed to the week level. Do you know what may cause this to happen?

Nothing should show on the 'other options' columns until you expanded the chart to the 'source' field like in the sample QVW. In my actual QVW, it is giving me the 'other options' when I have it collapsed to the week level.

[JonnyPoole]

I'm not sure.

[#7]

Has anyone else experienced this issue?

[Anonymous]

You need to provide an example qvw WITH the problem..

[#9]

I finally have an example!! Please help as I'm really having a lot of trouble with this.

Here we go - please look at the attached QV. When everything is collapsed o the 'calendar_week' level, there is a Total that I do not want to see. I only want to see those options if I expand the pivot table all the way to the 'Destination' column.

Please help!! any input is appreciated.

[Anonymous]

Hi,

On the Presentation tab for the pivot, you can select/deselect "Partial Sums" for all dimensions. If you deselect all, except for dimension "Destination", you will only see Totals when "Destination" is visible.

If you show "Partial Sums" for "Shipments", you will have one Total for each Shipment value - which is not what you want I guess?

Please show the attached qvw where I did these changes on the Presentation tab.