Sum on Matrix doesn't tie out

[NBOnecall]

I have this matrix being displayed I want to sum the amount of 30dayLostSales. This is the entire set of data with some filters, but the same obviously doesn't tie to what it should be.

Here is the code for what I believe is relevant.

30dayLostSales = [30DayDemandPerDay]*[CountDaysOOS]*[AvgSale]

30DayDemandPerDay = DIVIDE(AVERAGE('ns SalesRegister'[Rolling 30 days]),30)

CountDaysOOS = CALCULATE(COUNTROWS(FILTER('ns vwInventorySnapshot','ns vwInventorySnapshot'[DetereminedQuantity] =0 && 'ns vwInventorySnapshot'[Date] > MIN('ns vwInventorySnapshot'[FirstDateInStock]))))

AvgSale = AVERAGE('ns SalesRegister'[SalesAmount])

As always thank you for the help.

[pforrer7]

You should see this post: https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907 it explains what is happening and how to solve it.

[NBOnecall]

Thank you for that post. I applied that logic to my demand3 30DemandPerDayinStock measure and it still doesn't calulate the sum correctly.

30DayDemandPerDay = DIVIDE(AVERAGE('ns SalesRegister'[Rolling 30 days]),30)

30DayDemandPerDayTotal = var _table=SUMMARIZE('ns SalesRegister',[Rolling 30 days],"_value",[30DayDemandPerDay])
Return
If(HASONEVALUE('ns SalesRegister'[Rolling 30 days]),[30DayDemandPerDay],sumx(_table,[_value]))

[v-frfei-msft · Accepted solution]

Hi @NBOnecall ,

SUMX should help you. Please create another two measures to work on it.

M1= SUMX('ns SalesRegister',[30DayDemandPerDay])

M2 = SUMX('ns SalesRegister',[30DayDemandPerDayTotal])

If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

[NBOnecall]

I ended up doing a little bit more research and stumbled upon this formula and it worked for me.

30DaysLostSalesFix = IF(COUNTROWS(VALUES('ns NetsuiteItem'[InternalId]))=1,
[30dayLostSales],
sumx(VALUES('ns NetsuiteItem'[InternalId]),[30dayLostSales]))