Data Model Retail Business (help!!)

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!