Hello,
I have a pivot table that uses the MAX of one set of data. The problem I am having is when I see the grand total on the right of the pivot table this still shows the MAX rather than the sum of the MAX which is what I am looking for...
I saw older posts that solved this by adding the SUM OF MAX field to the data set used for the pivot table. I cannot get this to work because of the way the data is set up.
Some more detail...This is a financial model I am setting up which takes data from an accounting software and sorts it by custom fields such as Market, Submarket, Sales Channel (retail and direct), Region, Sales Target etc. Once I get the data all set up for the pivot table it is set so the sales target field is matched with every sale. If I sum the sales target field it would be the sales target x qty of sales which is not correct since the sales targets set by product by channel. Instead, I take the MAX (i could use MIN as well) because this will give me the total sales target per product by month.
The only other option I can think of is to get a SUM OF MAX column in my data. To do this I would need a formula that looks at a set of data and pulls out the sales target by item and channel once per month....
I hope that makes sense, thanks for any ideas!
I have a pivot table that uses the MAX of one set of data. The problem I am having is when I see the grand total on the right of the pivot table this still shows the MAX rather than the sum of the MAX which is what I am looking for...
I saw older posts that solved this by adding the SUM OF MAX field to the data set used for the pivot table. I cannot get this to work because of the way the data is set up.
Some more detail...This is a financial model I am setting up which takes data from an accounting software and sorts it by custom fields such as Market, Submarket, Sales Channel (retail and direct), Region, Sales Target etc. Once I get the data all set up for the pivot table it is set so the sales target field is matched with every sale. If I sum the sales target field it would be the sales target x qty of sales which is not correct since the sales targets set by product by channel. Instead, I take the MAX (i could use MIN as well) because this will give me the total sales target per product by month.
The only other option I can think of is to get a SUM OF MAX column in my data. To do this I would need a formula that looks at a set of data and pulls out the sales target by item and channel once per month....
I hope that makes sense, thanks for any ideas!
