Calc order in pivot table with COUNT
 

I have a simple pivottable in which I have used COUNT to figure out number of
stores per the data I have. I also have a total unit sales. I now want to
take the total units and divide by total stores to get units per store. What
it looks like it is doing is performing the division before it is summed, so
the units per store is the same as total units. I want it to calc after the
sum is done.

I have created two formulas and not seeing anything in Solve Order when I go
to this. What I have done to date is just set up a formula outside of the
pivot table and just do the division that way. The problem is is the table
changes, the formula may not be referring off to the correct cells (numbers)
anymore.

Any suggestions/directions would be greatly appreciated.

I am in Excel 2007.
--
Peter

Calc order in pivot table with COUNT
 

You'll only see Calculated Items in the Solve Order list, so your
formulas won't be there if they're Calculated Fields.

I'm not sure how you're getting the store count, but perhaps you could
add a column in your source data, with the heading Stores. In that
column, enter a 1 for each row of data.
In the pivot table, calculate the units per store by dividing the units
by the Store field.

peter wrote:
I have a simple pivottable in which I have used COUNT to figure out number of
stores per the data I have. I also have a total unit sales. I now want to
take the total units and divide by total stores to get units per store. What
it looks like it is doing is performing the division before it is summed, so
the units per store is the same as total units. I want it to calc after the
sum is done.

I have created two formulas and not seeing anything in Solve Order when I go
to this. What I have done to date is just set up a formula outside of the
pivot table and just do the division that way. The problem is is the table
changes, the formula may not be referring off to the correct cells (numbers)
anymore.

Any suggestions/directions would be greatly appreciated.

I am in Excel 2007.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Calc order in pivot table with COUNT
 

Debra - thanks for the response and good idea. I was just not thinking and
that will provide what I need.

The problem I was haveing was I was using COUNT instead of SUM, MAX, MIN,
etc. because the field I was initally using was not a numerical field. So, I
did just add a column and typed in a 1 and then filled it down.

thanks again!
--
Peter


"Debra Dalgleish" wrote:

You'll only see Calculated Items in the Solve Order list, so your
formulas won't be there if they're Calculated Fields.

I'm not sure how you're getting the store count, but perhaps you could
add a column in your source data, with the heading Stores. In that
column, enter a 1 for each row of data.
In the pivot table, calculate the units per store by dividing the units
by the Store field.

peter wrote:
I have a simple pivottable in which I have used COUNT to figure out number of
stores per the data I have. I also have a total unit sales. I now want to
take the total units and divide by total stores to get units per store. What
it looks like it is doing is performing the division before it is summed, so
the units per store is the same as total units. I want it to calc after the
sum is done.

I have created two formulas and not seeing anything in Solve Order when I go
to this. What I have done to date is just set up a formula outside of the
pivot table and just do the division that way. The problem is is the table
changes, the formula may not be referring off to the correct cells (numbers)
anymore.

Any suggestions/directions would be greatly appreciated.

I am in Excel 2007.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html