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

 

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

    I'm getting an error with the ns vwInventorySnapshot'[OutofStock] piece. Looks like it is not an available option for the SUMX formula.

     

    • masonlee01's avatar
      masonlee01
      Regular Visitor
      6 years ago

      Alright, i got it with some dummy data.

       

      invID       itemID       OOS         Price

      aa1225
      ba1246
      ca1367
      da1318
      ea1409
      fa14410
      ga15211
      ha16412

       

       

      avgPrice=AVERAGEX(data, data[Price])
blendedPrice=CALCULATE([avgPrice], ALLEXCEPT(data, data[itemID]))
totalOOS=SUMX(data, data[OOS])
lostSales=[totalOOS]*[blendedPrice]

       

       

      • masonlee01's avatar
        masonlee01
        Regular Visitor
        6 years ago

         

        You'll need to adjust to your tables & columns, but I think it's working