Hi
If you drag title to the Row area
Drag Period to the Column area
Drag Amount to the data area you get what you want
12 36 Grand total
Miss 1500 1500
Mr 6000 600 6600
If you don't want the 36 amount to show, just Hide
the column.
You can rename the heading in the PT from 12 to Yearly
You can rename the heading Grand Total to Amount (provided you make a
space after Amount, so it is not the same as the field heading in the
source table)
Regards
Roger Govier
NomadPurple wrote:
> Trying to make my pivot tables more useful by customizing my own formulas.
>
> I would prefer to use the following formula within a pivot table but can't
> seem to get the result I want. I'll add it to the source data if I have to
> but suspect my lack of pivot table knowledge is the problem.
>
> Assume Source data is
> Description Title Period Amount
> apples Miss 12 1000
> pears Miss 12 500
> geoff Mr 12 6000
> able Mr 36 600
>
> Formula I would add a column Yearly to the source data
> =if(period=36,0,Amount)
>
> and then take the total by Title
>
> ie I would like a pivot table giving the outcome (yes I need the sum of
> Amount so can't just filter by period)
>
> Title Amount Yearly
> Miss 1500 1500
> Mr 6600 6000
>
> I can't seem to insert an if statement in a calculate field and I need it to
> evaluate each piece of source data separately which (per excel help) seems to
> suggest I need a calculated item.
>
> It just can't be this complicated!
|
Similar Threads
