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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

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