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.

      • masonlee01's avatar
        masonlee01
        Regular Visitor
        6 years ago

        Without having the data, hard to play with.  Maybe this will work?

         

        lostSales = SUMX(SUMMARIZE('ns SalesRegister', 'ns SalesRegister'[InternalId], "myAvg", AVERAGE('ns SalesRegister'[Sales/Unit])), [myAvg] * 'ns vwInventorySnapshot'[OutofStock])