cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
JoeRandI Regular Visitor
Regular Visitor

Cross sell/upsell churn challenges (page filtering from different table on summed column)

Hey All,
I have a bit of a challenge. I'm working to understand how our ARR looks per month, which I've solved, and soft churn/hard churn, but I'm having difficulty looking at soft churn and upsell. I have one table that looks like this, looking at the contract line item level:
 
 
In order to do soft churn and upsell, I want to take the sum of the annual revenue and see where it changes. If its negative, thats churn. If its positive, thats upsell/cross-sell. If its the same, its renewal. The problem is this table has multiple values for date and customerID. So, I can't do a reverse lookup because it doesn't know what to look at. What I've done is created a summarized table of Customer ID, Date, and summed the ACV, like this:
 Post image
 
I then created a column in each table called (Customer ID + Date), so it creates a unique column in my second table, and created a relationship with the first.
The challenge I have is that I need to be able to dynamically filter the annual revenue (and in the next phase, the soft churn/hard churn lookup values), based on Product/Type, and in the future perhaps region from a different datasource. However, the annual revenue column in the second table doesn't filter from page level filters provided by the original table. If I bring in Product/Type to the new table, I again have the challenge of multiple rows for each date and customer ID, losing unique values.
 
Does anyone have any ways to go about doing this, or otherwise get the soft churn/upsell/renewal values from the original table?
 

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors