Pivot table calculated fields

LGFN1

Board Regular
Joined
Jun 25, 2014
Messages
63
I have a sheet with sales for a couple of months, and I'd like to have a chart show me the sales of months and how the price change per unit has affected its sales. So in other words a typical price/value chart. There's a caveat, though, I don't have the explicit data of when the price change has taken effect, I'm determining it from how much the customer paid per unit.

For your info, here's the steps I did to create the chart I have now (using Excel 2013):
  1. Created a pivot table
  2. Added "Date" in the Rows section
  3. Added "Quantity" and "Price per Unit" in the Values section, setting them to Sum and Average respectively
  4. Added "Item Number" in the Filters section
  5. Group Fielded "Date" as "Days" and "Months", and moved the auto generated "Months" from the Rows to the Filters section
  6. Created a pivot chart with chart type as Combo, and set the "Average of Price per Unit" as a secondary axis
Which should work, but there's a problem here that the chart will only show dates that have values, so I won't see dates that didn't have any sales at all. I wouldn't want that, I need to see the overall picture even if there were no sales.

So I went to the Field Settings and checked "Show items with no data".

Now we have another problem: the table/chart shows all dates of the entire year, I don't even have the data for the entire year. So for this I have the "Months" filter handy and I choose a specific month I'd like to analyze. (Bug alert: if you check "Select Multiple Items" in the bottom of the filter menu, the entire table/chart still shows, but the data is only for the checked month(s).)

The problem here is, that since I don't have explicit data on when unit prices changed, and I only determine it from sales, I have a bunch of blank rows in the table for days that had no sales, causing odd spikes in the chart (see image: https://www.dropbox.com/s/7dh61hke7fk8g4j/Odd Spikes.JPG, the orange line is the price per unit, the blue is the quantity sold).

I assume the best way to fill in the blank is somehow through a Calculated Field. And here's where I'm stuck. I started writing an IF statement, as follows:

Code:
=IF('product sales'=0, ?? ,'product sales'/quantity)

The questions marks is where I'd like to reference "the previous populated row in this column", so the price line will stay put till there's a sale with a change of "Price per Unit" to it.

Is this a possibility? If not, is there a different/better way to achieve the desired results?
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Watch MrExcel Video

Forum statistics

Threads
1,122,732
Messages
5,597,802
Members
414,176
Latest member
LK88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top