Forum Discussion
Measure total is not correct
I'm getting an error with the ns vwInventorySnapshot'[OutofStock] piece. Looks like it is not an available option for the SUMX formula.
Alright, i got it with some dummy data.
invID itemID OOS Price
| a | a12 | 2 | 5 |
| b | a12 | 4 | 6 |
| c | a13 | 6 | 7 |
| d | a13 | 1 | 8 |
| e | a14 | 0 | 9 |
| f | a14 | 4 | 10 |
| g | a15 | 2 | 11 |
| h | a16 | 4 | 12 |
avgPrice=AVERAGEX(data, data[Price])
blendedPrice=CALCULATE([avgPrice], ALLEXCEPT(data, data[itemID]))
totalOOS=SUMX(data, data[OOS])
lostSales=[totalOOS]*[blendedPrice]
You'll need to adjust to your tables & columns, but I think it's working
So all these equations work out, but again I think I am in the same boat as before. The total lines for the Lost Sales does not sum correctly. From my understanding I have to use a filter equation to sum the rows that are on my visual.
Thank you for you help so far.
- What is it summing too? The average of all prices times the sum of all out of stock instead of the individual lost sales?
- I’m not at office, but probably needs to be an iterator or calculate statement in the lost sales. Try wrapping it with calculate or sumx instead of just adding the measures together. You want to force it to go row by row multiplying then adding, not taking the grand totals and multiplying.
The Lost Sales sum to a little of 16k when exporting the data to Excel, but it is showing about 51k on the total line in Power BI.
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])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.
I created a powerBI report with a few example measures that seem to work and add things up correctly.
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])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.