cancel
Showing results for
Did you mean:
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:

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?

4 REPLIES 4
Highlighted
Community Support

## Re: Product Revenue Cannibalization

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.
Helper I

## Re: Product Revenue Cannibalization

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

## 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!
Helper I

## Re: Product Revenue Cannibalization

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

Announcements

#### ‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors