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

So all these equations work out, but again I think I am in the same boat as before. The total lines for the Lost Sales does not sum correctly. From my understanding I have to use a filter equation to sum the rows that are on my visual.

 

Thank you for you help so far.

 

masonlee01's avatar
masonlee01
Regular Visitor
6 years ago
What is it summing too? The average of all prices times the sum of all out of stock instead of the individual lost sales?
  • masonlee01's avatar
    masonlee01
    Regular Visitor
    6 years ago
    I’m not at office, but probably needs to be an iterator or calculate statement in the lost sales. Try wrapping it with calculate or sumx instead of just adding the measures together. You want to force it to go row by row multiplying then adding, not taking the grand totals and multiplying.
  • NBOnecall's avatar
    NBOnecall
    Icon for Helper V rankHelper V
    6 years ago

    The Lost Sales sum to a little of 16k when exporting the data to Excel, but it is showing about 51k on the total line in Power BI. 

     

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

    Here is the visualization when filtered down to just 1 item in 2 different locations.

     

     

    As you can see the "Lost SalesTest" column does not sum correctly. Here are my forumulas.

     

    Lost SalesTest = [totalOOS]*[blendedPrice]*[avgSalesPerDay]
    totaloos = sumx('ns vwInventorySnapshot', 'ns vwInventorySnapshot'[OutofStock])
    blendedprice = CALCULATE([avgSalesPerUniTest],ALLEXCEPT('ns NetsuiteItem','ns NetsuiteItem'[InternalId]))
    avgSalesPerDay = AVERAGEX('ns SalesRegister', 'ns SalesRegister'[SalePerDay])
  • NBOnecall's avatar
    NBOnecall
    Icon for Helper V rankHelper V
    6 years ago

    So I am at a lost of what Forumula I need on the measure to get the total line to SUM the measure instead of performing the Measure on the total line. I feel like I have tried countless other suggestions found throughout the forum, but still end up not getting the correct sum on the total line.

  • masonlee01's avatar
    masonlee01
    Regular Visitor
    6 years ago

    I created a powerBI report with a few example measures that seem to work and add things up correctly.

     

    Here

     

    I think the simplest is:

     

    blendedPrice = CALCULATE([avgPrice], ALLEXCEPT(data, data[itemID]))
lostSales = sumx(data, [totalOOS]*[blendedPrice])

     

    But I have an example that uses summarize, addcolumns & allselected.  Maybe it's a bit more robust.

    blendedPrice3 = AVERAGEX(ADDCOLUMNS(SUMMARIZE(data, data[itemID]), "bp3", CALCULATE(AVERAGEX(ALLSELECTED(data), data[Price]))), [bp3])

     

     

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

    Thank you for your help! It now sums correctly, but only if one of my locations are selected.

     

    Here are the formulas I am using:

     

    lostSalesExample = sumx('ns NetsuiteItem',[totaloos]*[blendedAvgSale]*[avgSalesPerDay])
    avgSalesPerDay = AVERAGEX('ns SalesRegister', 'ns SalesRegister'[SalePerDay])
    blendedAvgSale = CALCULATE([AvgSale],ALLEXCEPT('ns NetsuiteItem','ns NetsuiteItem'[InternalId]))
    totaloos = sumx('ns vwInventorySnapshot', 'ns vwInventorySnapshot'[OutofStock])

     

    The NetSuiteItem table does not have the locations, it is another table called Locations.

     

    Thank you again.