Simulation using What If? parameters – calculating potential cannibalization effects

Greetings!

I’ve recently started dabbling with the What If? parameters in PBI – incredible stuff that would really help our organization. Now to my question – how to make a simulation that is a product of another simulation work properly.

I have several calculated tables that are a replica of my product table. They consist of the formula:

Simulation Table X  =

Simulation Table X =
CROSSJOIN ( 
   GENERATESERIES ( -0.2, 0.2. 0.01 ),
   d_article
)

I.e. all unique products are written out on X-amount of rows, the number of rows being each increment in the series generated.

Currently I have 4 calculated tables that simulates

• Unit cost
• Unit price
• Demand (quantity sold)
• Cannibalization

Cannibalization is slightly different and has a generateseries ranging from 0.00 - 1, with 0.05 increments. No negative numbers, in other words.

Each table has a one-to-many inactive relationship with the original product_table (containing unique product keys). See model below:

Each table has the same relationship to my product_table:

The first three tables (price, cost & demand) work like a charm, and I can select the simulated rate and then use the simulation table as slicers so that the user can specify what products the simulation should be applied to. Rather, my problem is with the last table – cannibalization.

This is probably due to its behavior being quite different. Or rather, the result of its simulation is dependent on what demand simulation we are using. My initial idea was that the user can select a value here, ranging from 0% to 100%, which in turn is is calculated by:

Simulated Cannibalization * Change in Quantity Sold

Where Change in Quantity Sold is:

# Quantity Simulated - # Quantity

Cannibalization rate can be 0% to 100% of the change in demand. Also, the cannibalization rate cannot exceed the demand change. The idea behind this is that if some product is selling a lot more (such as new products), it’s very likely that most of its increase in quantity sold is taken from somewhere else (i.e. cannibalization). So if a product increase its demand by 10% and has a original quantity sold of 1 000, the simulated quantity sold would be 1 000 * 1,1 = 1 100. With a Cannibalization rate of 50% the cannibalization would be (1 100 - 1 000) * 0,5 = 50.

I want to be able to show this in a matrix using the original product table. Rows in the matrix is taken from the product table.

I.e. the cannibalization should appear on the row for the selected product in slicer Apply Cannibalization Simulation To.

As you can see, Double menus has a 10% higher quantity in # Quantity simulated due to the Demand selection being 10%, and 50% of the increased quantity sold is equal to 4 422. However, I want the measure # Cannibalization to appear on the row for Chicken menus, not Doubles.

The measure for calculating the simulated quantity sold is as follow:

# Quantity simulated (selected) =
VAR Simulation = ( 1 + 'Simulation - Demand'[Simulation - Demand Value] )
VAR Demand_selected =
    CALCULATE(
        [# Quantity] * Simulation,
        FILTER(
            d_article,
            d_article[article_key] IN VALUES('Simulation - Demand'[article_key])
        )
    )

VAR Demand_selected_not =
CALCULATE(
    [# Quantity],
    FILTER(
        d_article,
        NOT d_article[article_key] IN VALUES('Simulation - Demand'[article_key])
    )
)

RETURN
Demand_selected + Demand_selected_not

My current # Cannibalization measure reads as follow:

# Cannibalization =
VAR Simulation = ( 'Simulation - Cannibalization'[Simulation - Cannibalization Value] )
VAR Net_increase = [# Quantity simulated] - [# Quantity]
VAR Result =
    CALCULATE(
        Net_increase * Simulation,
        FILTER(
            d_article,
            d_article[article_key] IN VALUES('Simulation - Cannibalization'[article_key])
        )
    )

RETURN
Result

I’m not sure how to change the syntax for it to work the way I want it to work.

Many thanks in advance PBI Community!