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 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.