Forum Discussion
Helper VMeasure total is not correct
Hi,
Need some help and yes I know there are a dozen of threads for this same exact issue, but whatever I am doing in relation to those answers isn't working for me.
My measure is the following:
Lost Sales = sum('ns vwInventorySnapshot'[OutofStock])*AVERAGE('ns SalesRegister'[Sales/Unit])*AVERAGE('ns SalesRegister'[SalePerDay])
I have items that can be in two different locations that are in the visual. Here is the visual.
Hopefully you all can help me figure out what formula is needed to get a correct Lost Sales total.
Thank you,
Noel
16 Replies
Assuming 'ns vwInventorySnapshot' and 'ns SalesRegister' are two tables joined by InternalID, try this
avgSalesPerUnit = AVERAGEX('ns SalesRegister', 'ns SalesRegister'[Sales/Unit]) Lost Sales = CALCULATE(SUM('ns vwInventorySnapshot'[OutofStock]) * [avgSalesPerUnit])If that doesn't work try
avgSalesPerUnit = AVERAGEX('ns SalesRegister', 'ns SalesRegister'[Sales/Unit]) Lost Sales = CALCULATE(SUM('ns vwInventorySnapshot'[OutofStock]) * [avgSalesPerUnit], ALL('ns SalesRegister'))ALL might need to be ALLSELECTED or KEEPFILTERS - I'm not great with those.
NBOnecallThis doesn't get me the numbers we need. Actually using the Lost Sales mesaure it doesn't even multiple the OutofStock correctly, it just looks like the OutOfStock is just assuming 1 it looks like. The output of avgSalesPerUnit equals Lost Sales measure.
Thanks,
Noel
Is the pricing averaging out correctly across the units in mult locations? Is there a relationship defined between those tables?
