Data Model Retail Business (help!!)

[Anonymous]

Hi Guys,

I work on the retail business (several stores and several products) and i would like to make a report about:

1) Products with NO SALES

2) Products with stock coverage:

Products with > 50 days of stock

Producst with 20 to 50 days of stock

Products with < 20 days of stock

On my datal model i have:

Fact tables:

Stock table (data granularity - month) 

Sales table (data granularity - month) 

Dimension tables:

Data

Produt

Assumptions:

Stock coverage = Stock of product x at the store A dividing by Sales of the product x at the Store A

Stock with no sales = Product who make part of the products table but doesnt have any record of sales on the same month. 

So acording with this assumptions im dealing with a big problem (at least for me obviouslly...).  To calculate the "Stock coverage" its necessary to make  calculations on rows (X Functions), but i have no chance to make them between diferent fact tables (stocks and sales). So how is the best way to do it?

Thank you so much!

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format?

for no sales you can try like

if( isblank(CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))) && CALCULATE(SUM(Item[Qty]),DATESMTD('Date'[Date])) >0, "Stock with no sales",blank())

[Anonymous]

@amitchandak thank your the quick answer.

Please find bellow the sample of the data model. 

https://drive.google.com/file/d/1PzNI0i4pL094meB_0eg7wwRxm52K1jy9/view?usp=sharing 

Please be welcome to put the measures inside on it. No worries.

Thanks in advance again.

[richbenmintz]

Hi @Anonymous,

Can you provide a table  with your expected values, I am trying to understand why you need an 'X' function seems like a straight divide to me. 

Thanks,

[Anonymous]

Hi again,

Thank all for your support. Well i wasn`t clear enought on my first email, so will try to be more specific.

The final layout could be something like that, in diferent variables (value of stock, % of total stock, etc). But the vision will be always by store, by stock coverage and by total stores.

But the problem is with the total (red highlighted) on the pic above. And why?

Let see the following example of the same artcile (123) on several stores. The agregated data will give me 110 of stock for 110 of sales (30 days of stock, meaning "less than 50 days". However the is not real info. 

I hope that i can be clear now. For that reason i have a new link for drive with the Power BI file as well as the "excel version":

https://drive.google.com/drive/folders/1Hx1dVA_SdO7-Wt1qCJ5GEhusSGhAh6KR?usp=sharing

[richbenmintz]

Hi @Anonymous,

Ok so probably not the most elegant Solution, but given that you are looking to infer dimension values I created 3 Columns for you in the F_Stock Table

Coverage Days = 
var val = DIVIDE('F_Stock'[Stock Value], 'F_Stock'[Sales Value])*30
return
if(val = 0, 0, val)

Coverage Label = 
    SWITCH(
        TRUE(),
            'F_Stock'[Coverage Days] = 0, "No Sales",
            'F_Stock'[Coverage Days] < 20, "Less than 20 Days",
             'F_Stock'[Coverage Days] < 50, "Less than 50 Days",
             "More than 50 Days"
    )

//to be used as the sort by column for Coverage Label
Coverage Index = 
    SWITCH(
        TRUE(),
            [Coverage Days] =0, 4,
            [Coverage Days] <= 20, 1,
            [Coverage Days] <= 50, 2,
            [Coverage Days] > 49, 3
    )

You can then create a matrix like the following

You can also create the calc columns in Power Query by join the stock and sales tables together or adjust your source queries to get the stock and sales in the same table. but for now this seems to do the trick

hope this helps,

Richard

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

[Anonymous]

Hi Richard,

Thans for the reply. The table looks superb,

Is it possible to share the model? Im still very beginner so its dificult to me to understand the explantion just based on code.

Thanks!

[richbenmintz]

Hi @Anonymous,

Sure thing, link here, link to pbix.

In words essentially what i created where three calculated columns in the F_Stock Table

[Coverage Days] = stock / sales *30

[Coverage Label] = Case statement to assign correct label based on [Coverage Days] Value

[Coverage Index] = Case statement to assign correct order for [Coverage Label] based on [Coverage Days] Value This value is used as the sort by value for the [Coverage Label] Column

Once the Calculated Columns are available you can create the matrix you are looking for

[Anonymous]

@richbenmintz Uau this looks amazing!!

In onder to understand better the solution:

Is is possible to explain why did you make the [coverage days_], [coverage index_] and [coverage label_] also on the "F_sales"? Any reason or this is not used? From my understanding it seems not to be used by the model.

At the same time, it would be feasible to add to more columsn on the F_Stock with sales of "month n-1" and "month n-"2? And at the same time to adapt the coverage days formula from "stock / sales" to "stock / average sales (month n, n-1,n-2)" (i make a picture - below - in order to facilitate what im triying to say)

If i get your logical, i can adapt it to my reality.

Again and again...all my respect and thanks for your extraordinary help!

André

[richbenmintz]

Hi @Anonymous,

The Columns in the F_Sales sales table are not really relevent, just a product of initial tests.

I will review the Month - N ask in a little while.

Thanks

[richbenmintz]

Hi @Anonymous,

I mocked up a disconnected dimension approach to make the stock value by day inventory a little more dynamic which will allow you to perform date look backs a little more on the fly without having to create columns for each permutation.

Solution as follows:

Disconnected Dimension - Create Dax Calc Table:

Coverage = DATATABLE(
        "Coverage", STRING, 
        "Coverage Index", INTEGER, 
        {
            {"More than 50 Days", 3},
            {"No Sales", 4},
            {"Less than 20 Days", 1},
            {"More than 20 Days", 2}
        }
    )

Current Period Measure:

stock Value Dynamic CP = 
var tbl = ADDCOLUMNS('F_Stock',"coverage", divide([Stock value], [Sales value], 0)*30, "visible", 
    switch(true(),
        [Sales value] = BLANK() && min(Coverage[Coverage Label]) = "No Sales", [Stock value],
        [Sales value] > 0 && divide([Stock value], [Sales value])*30 < 20 && min(Coverage[Coverage Label]) = "Less than 20 Days", [Stock value],
        [Sales value] <> 0 && divide([Stock value], [Sales value])*30 >=20 && divide([Stock value], [Sales value])*30 < 50 && min(Coverage[Coverage Label]) = "Less than 50 Days", [Stock value],    
        [Sales value] > 0 && divide([Stock value], [Sales value])*30 >= 50 && min(Coverage[Coverage Label]) = "More than 50 Days", [Stock value] 
        ))
var ret = if(HASONEVALUE(Coverage[Coverage Label]), CALCULATE(SUMX(tbl, [visible])), sum([Stock value]))
return
ret

Current Period Measure Prior Period:

stock Value Dynamic CP = 
var tbl = ADDCOLUMNS('F_Stock',"coverage", divide([Stock value], [Sales value], 0)*30, "visible", 
    switch(true(),
        [Sales value] = BLANK() && min(Coverage[Coverage Label]) = "No Sales", [Stock value],
        [Sales value] > 0 && divide([Stock value], [Sales value])*30 < 20 && min(Coverage[Coverage Label]) = "Less than 20 Days", [Stock value],
        [Sales value] <> 0 && divide([Stock value], [Sales value])*30 >=20 && divide([Stock value], [Sales value])*30 < 50 && min(Coverage[Coverage Label]) = "Less than 50 Days", [Stock value],    
        [Sales value] > 0 && divide([Stock value], [Sales value])*30 >= 50 && min(Coverage[Coverage Label]) = "More than 50 Days", [Stock value] 
        ))
var ret = if(HASONEVALUE(Coverage[Coverage Label]), CALCULATE(SUMX(tbl, [visible])), sum([Stock value]))
return
ret

Couple Caveats:

- Date Math is not very robust just subtracts one from the Max Month in context

- Better to have a YearMonth column in the date table like, 202010, then subtracting 100 will always return the prior month

- Assumes that Month will be in the context as it uses Max to get current month and then subtracts 1, may be better to check to see if Month has a single value and then perform the calc else return blank()

I all new formulas saved to shared pbix

Hope this helps you out,

Richard

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

[Anonymous]

Hi,

Thanks for the input!

I will test it and then came to give feedback.

Cheers and thank you again

[Anonymous]

Hi @richbenmintz 

The solution is working perfectly!

I was trying on the final model to show only in percentages. I donne it but know i found an error.

For example, in the month 11, Paris Store. Yellow highlighted.

I was trying to just show the "More than 50 days" and the "No sales" on the table below and, at the same time, the percentages of these two "labels", according with TOTAL stock of the store (and not just the ones i selected "more than 50 days" and the "no sales").

However, when i select the "no sales" and the "more than 50 days" i have a % of 100% for the total of the store. However the righ percentage should be 128 / 179 = 71,5% and not 128 /128 = 100%. I hope i make myself clear on these explanation. Is there any chance to bypass this?

Cheers!

[richbenmintz]

Hi @Anonymous,

Can you provide the measure you created? will be easier for me to understand what needs to be changed.

Thanks,