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

[Anonymous]

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:

 

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?