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

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.