Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Data Model Retail Business (help!!)

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 >...
Need Help
Tips and Tricks
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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())

  • richbenmintz's avatar
    richbenmintz
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        richbenmintz
        Icon for Resident Rockstar rankResident Rockstar
        6 years ago

        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!