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

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.