Hi there,
I have monthly profit data for 2014. I'm trying to add cumulative profit by monthly cohort in a pivot table. The "Show Value As > Running Total In" function almost works perfect, except I want to leave the cell blank if there is no data to add to the running total. The reason is because I don't want to show a flat line in a line chart if there is no data. Please see attached images. I've also thought about creating a "Cumulative Profit" column with a formula that takes the 1st purchase month (G1), and cohort month (K1) and returns the cumulative profit from C1 or something. I'm so suck.
Data
Pivot data.PNG
Pivit 1 - No calculation
Profit pivot.PNG
Pivot 2 - Cumulative profit
Cumulative profit.PNG
Pivot 2 chart
Cumulative profit chart.PNG
I've spend 12+ hours trying to figure this out so any help would really be appreciated. Thanks.
Bookmarks