Combing Obsolete & current SKU sales for an aggregated view

[safifaruqi]

Hi Everyone,

Thank you for taking the time to read through my query. 

I am trying to create a sales report which provides historical sales view at the Item level i.e. SKU level.

Here is the challenge :

SKU A -> Sales 100 $, at somepoint during the year, SKU A was phased out and replaced by SKU B, SKU B Sales $100

for reporting I want to display the total historical sales for the SKU combined (Exhausted SKU Sales + Replaced SKU Sales ( combined  ) reported under the Currently Active SKU, in our example SKU B

SKU B -> Sales 200 $ (merging the historical sales of SKU A with SKU B, as basically they are they same sku with some engineering improvement). 

I am struggling with the Data Structure and how I should set it up, as there are more than 5 instances of SKUs being obsoleted and replaced with newer SKU and to combine historical sales of 5 SKUs to be reported under the currently active SKU is really confusing me. 

Any guidance with respect to approach would be super helpful

[v-yanjiang-msft · Accepted solution]

Hi @safifaruqi ,

According to your description, I create a sample by my understanding, A is replaced by B, and C is replaced by D.

Create new measures for SKU B and SKU D.

Total SKU B = 
SUMX (
    FILTER ( ALL ( 'Table' ), 'Table'[SKU] IN { "A", "B" } ),
    'Table'[Sales]
)

Total SKU D = 
SUMX (
    FILTER ( ALL ( 'Table' ), 'Table'[SKU] IN { "C", "D" } ),
    'Table'[Sales]
)

Get the total value.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[safifaruqi]

thank you so much, apologies for the late reply as I was away on Holiday, but I will try that and but, my only concern is that would I have to make modifications to the dax code everytime a new sku is added? is there a way to have values roll-up naturally? or maybe I am not understanding the solution properly

[v-yanjiang-msft]

Hi @safifaruqi ,

If you don't want to modify the formula everytime a new SKU is added, you should create tables record the logic of SKU changed.

For example in this sample.

the logic of SKU changed is A>B>E, C>D. Here's my solution.

1.Create two tables.

SKU1

SKU2

2.Create two measures.

Total SKU 1 =
SUMX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[SKU]
            IN SELECTCOLUMNS ( 'SKU1', "SKU before", 'SKU1'[SKU before] )
                || 'Table'[SKU] IN SELECTCOLUMNS ( 'SKU1', "SKU now", 'SKU1'[SKU now] )
    ),
    'Table'[Sales]
)

Total SKU 2 =
SUMX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[SKU]
            IN SELECTCOLUMNS ( 'SKU2', "SKU before", 'SKU2'[SKU before] )
                || 'Table'[SKU] IN SELECTCOLUMNS ( 'SKU2', "SKU now", 'SKU2'[SKU now] )
    ),
    'Table'[Sales]
)

3.Get the expected result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

[helenhelen]

if do not create SKU1 and SKU2 table. will your dax measure works? 

[Greg_Deckler]

@safifaruqi Couple ways to handle it. You could do a replace when importing. Another way would be to have a table of SKU's with 2 columns. One column has all SKU's that have ever existed. Second column has what each SKU is today.

[helenhelen]

@Greg_Deckler  i did create One column has all SKU's that have ever existed. Second column has what each SKU is today. for example: if this sku has been superseded by 3 times, what dax formular should i program to get total sum for this sku? thanks very much

[safifaruqi]

Hi @Greg_Deckler 

Thank you for your response and my apologies for the late reply, I am not aware of a replace when importing, canyou point towards a resource where I can read up that solution. With regards to a seperate table, that is what I initially thought of as well, here is my follow-up question :

would you then create a hierarchy in PowerBI to denote Parent Child ? SKUs that ever existed become child, where-as the current sku becomes Parent, till its phased out ? 

Thank you so much for your help, 

Regards,