Forum Discussion

Halt1234's avatar
Halt1234
Regular Visitor
3 years ago

Forecasting Substitute Products

Hi,

 

I am in the early days of creating a data model ( trying to learn through YouTube,books and this forum, but I haven't found how to do the below,though it might be to do with what I am searching for) 

 

The current scenario that I need to be able to deal with is where for example we currently buy Product X (15 pounds for 1kg) but this has now been substituted for Product Y (Product Y is a different price and size, e.g. 5 pounds for 400grams). 

 

We therefore have the historic sales Volumes for Product X which I want to use for Product Y but the new forecasted sales price.

 

At the moment my thought process is a 

Sales fact table - holds the historic sales volumes

Calendar table

 

Then to potentially have a table which shows product Y = Product X

 

Forecast fact table,which at the moment would be a straight copy of the actuals table with a % uplift on certain periods but needs to  use product X with product Y sales volume.

 

But this is where I get stuck, is there any examples of something similar anywhere that I can look at, nothing has been set in stone yet I am trying to get the data model designed on paper first before building anything. 

Need Help

1 Reply

  • rbriga's avatar
    rbriga
    Impactful Individual
    3 years ago

    Putting forcasts aside, let's reduce this problem into converting purchases from products to their subtitues, if any.

    I assume that your tables look like this (putting aside date, customers and more):

    The DAX is quite complicated- we need to tinker with the row context quite a bit.

    For example, product X return results even when it doesn't feature in the Fact table.

    This is my DAX:

    New Purchases Test =
SUMX (
    'Dim Products',
    VAR _Substitute =
        SELECTEDVALUE ( 'Dim Products'[Subtitue] )
    VAR _Has_Substitue =
        NOT ( OR ( ISBLANK ( _Substitute ), _Substitute = "" ) )
    VAR _Product =
        SELECTEDVALUE ( 'Dim Products'[Product] )
    VAR _Substitue_Table =
        CALCULATETABLE (
            'Dim Products',
            REMOVEFILTERS (),
            KEEPFILTERS ( 'Dim Products'[Subtitue] = _Product )
        )
    VAR _Is_Substitute =
        COUNTROWS ( _Substitue_Table ) > 0
    RETURN
        SWITCH (
            TRUE (),
            _Has_Substitue, BLANK (),				--If there is a substitute, the product will return a blank
            SUM ( 'Fact Purchases'[Quantity] )		--If the product has no substitute, it returns its own purchases, plus purchases of the product it replaces	
                + IF (
                    NOT ( _Is_Substitute ),
                    BLANK (),
                    DIVIDE (
                        SUMX (						--A table of the replaced products
                            CALCULATETABLE (
                                'Dim Products',
                                REMOVEFILTERS (),
                                KEEPFILTERS ( 'Dim Products'[Subtitue] = _Product )
                            ),
                            CALCULATE (
                                SUM ( 'Fact Purchases'[Quantity] ),				--Quantity of the replaced products, 
                                REMOVEFILTERS ( 'Fact Purchases'[Product] )
                            ) * 'Dim Products'[Weight (KG)]						--multiplied by the weight of the replaced product,
                        ),
                        'Dim Products'[Weight (KG)]								--divided by the weight of the new product
                    )
                )
        )
)

    This makes sure that:

    1. Replaced products wouldn't show up
    2. New products would show their own purchases plus those of the products they replace, factored by weight
    3. Non-replaced products compute normally.