Forum Discussion

NBOnecall's avatar
NBOnecall
Icon for Helper V rankHelper V
6 years ago

Measure 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 followin...
Need Help
masonlee01's avatar
masonlee01
Regular Visitor
6 years ago

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.

  • NBOnecall's avatar
    NBOnecall
    Icon for Helper V rankHelper V
    6 years ago

    This 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

    • masonlee01's avatar
      masonlee01
      Regular Visitor
      6 years ago

      Is the pricing averaging out correctly across the units in mult locations?  Is there a relationship defined between those tables?

      • NBOnecall's avatar
        NBOnecall
        Icon for Helper V rankHelper V
        6 years ago

        The avgSalesPerUnit matches my Average of Sales/unit columns.

         

        SalesRegister and InventorySnapshot is linked via internal ID of NetsuiteItem table.