cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ccsrtw Helper I
Helper I

Product Revenue Cannibalization

I'm trying to prove or disprove the notion that one of our products (let's call it "X") is cannabilizing revenue from the others ("Other").  

 

My data model/structure is relatively straightforward, I have a fact table ("AllOrders") which is linked to a date table ("Calendar") and a customer table ("CustomerTable"). The orders are subscription based.

 

My first step was to define four unique measures:

 

1) Current Month $ X = CALCULATE( TOTALMTD( SUM( [Revenue[), 'Calendar'[DateKey]), AllOrders[Product] = "X")

 

2) Current Month $ Other = CALCULATE( TOTALMTD( SUM( [Revenue[), 'Calendar'[DateKey]), AllOrders[Product] = "Other")

 

3) Prior Month $ X = CALCULATE( SUM( [Revenue]), PREVIOUSMONTH('Calendar'[DateKey]), AllOrders[Product] = "X")

 

4) Prior Month $ Other = CALCULATE( SUM( [Revenue]), PREVIOUSMONTH('Calendar'[DateKey]), AllOrders[Product] = "Other")

 

Using these four measures, I created a fifth measure called "Shifted Revenue".

 

5) Shifted Revenue =

 

=CALCULATE (
    MIN ( [CM $ X] - [PM $ X], [PM $ Other] - [CM $ Other] ),
    FILTER (
        CustomerTable,
        [CM $ X] > [PM $ X]
            && [CM $ Other] < [PM $ Other]
            && TOTALMTD ( [Sum of Revenue], 'Calendar'[DateKey] ) > 0
    )
)

 

 

Good news is that this works and captures the right value that I want in the month that X cannibalizes Other revenue, but I need to take it one step further.

 

Using an example:

 

Screenshot_1.png

 

 

 

 

 

My "Shifted Revenue" measure captures the 2,000 in 4/1/2017, but I also want it to continue on and pick up the subsequent months since that revenue is something I would consider cannibalized as well, not just the first month that it shifts from Other to X. Currently drawing blanks on how best to do this. Any ideas?

 

Thanks for your time!

4 REPLIES 4
Community Support
Community Support

Re: Product Revenue Cannibalization

@ccsrtw,

 

For the first step, instead of measure, you may try adding a calculated table.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ccsrtw Helper I
Helper I

Re: Product Revenue Cannibalization

Hi @v-chuncz-msft,

 

Appreciate the reply - not sure what that would accomplish? I'm somewhat familiar with the concept of calculated tables, but haven't really worked with them.

Super User I
Super User I

Re: Product Revenue Cannibalization

Hi @ccsrtw

 

 

Can you post some sample data on one drive and share the link here and the output expected.

 

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
ccsrtw Helper I
Helper I

Re: Product Revenue Cannibalization

@CheenuSing - sure! I'll mock something up and post later today or tomorrow. Appreciate your reply.

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors