Forum Discussion
Helper VSum on Matrix doesn't tie out
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.
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.
4 Replies
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.
NBOnecallThank 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-msftCommunity Support
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.
