Retention Analysis

chrisco

New Member
Joined
Dec 17, 2015
Messages
10
Greetings all!

I'm trying to create a report that will allow me to walk from 2016 sales to 2017 sales. I need to create a waterfall graph as well as a pivot that shows:

2016 Base
add:
New Sales
Upsells
Less:
Lost Sales
Downsells
Total should equal 2017 Sales.

I also need to show this from a [Account] level, [Product Family] and finally a [Product] level.

I've created measures for [2016 Sales] and [2017 Sales] as well as the [Variance] and the related categories ([New], [Upsell], [Downsell], [Loss])

I have created the logic to assign items to each category listed above, but I'm encountering 2 issues that have me stumped.

1)Every product in the table appears when I drill all the way down to that level, regardless if there was any activity. This only appears when I start adding the columns for [New], [Upsell], [Downsell], [Loss].

2)I can't seem to get the activity at the lower levels to bubble up. I know this would be a case for Sumx, but I'm honestly not that familiar with it.

Any tips or points in the right direction would be appreciated.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Forum statistics

Threads
1,223,889
Messages
6,175,223
Members
452,620
Latest member
dsubash

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
Back
Top