Measure total is not correct

[NBOnecall]

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 following:

Lost Sales = sum('ns vwInventorySnapshot'[OutofStock])*AVERAGE('ns SalesRegister'[Sales/Unit])*AVERAGE('ns SalesRegister'[SalePerDay])

I have items that can be in two different locations that are in the visual. Here is the visual.

Hopefully you all can help me figure out what formula is needed to get a correct Lost Sales total.

Thank you,

Noel

[masonlee01]

Assuming 'ns vwInventorySnapshot' and 'ns SalesRegister' are two tables joined by InternalID, try this

avgSalesPerUnit = AVERAGEX('ns SalesRegister', 'ns SalesRegister'[Sales/Unit])
Lost Sales = CALCULATE(SUM('ns vwInventorySnapshot'[OutofStock]) * [avgSalesPerUnit])

If that doesn't work try

avgSalesPerUnit = AVERAGEX('ns SalesRegister', 'ns SalesRegister'[Sales/Unit])
Lost Sales = CALCULATE(SUM('ns vwInventorySnapshot'[OutofStock]) * [avgSalesPerUnit], ALL('ns SalesRegister'))

ALL might need to be ALLSELECTED or KEEPFILTERS - I'm not great with those.

[NBOnecall]

This doesn't get me the numbers we need. Actually using the Lost Sales mesaure it doesn't even multiple the OutofStock correctly, it just looks like the OutOfStock is just assuming 1 it looks like. The output of avgSalesPerUnit equals Lost Sales measure.

Thanks,

Noel

[masonlee01]

Is the pricing averaging out correctly across the units in mult locations?  Is there a relationship defined between those tables?

[NBOnecall]

The avgSalesPerUnit matches my Average of Sales/unit columns.

SalesRegister and InventorySnapshot is linked via internal ID of NetsuiteItem table.

[masonlee01]

Without having the data, hard to play with.  Maybe this will work?

lostSales = SUMX(SUMMARIZE('ns SalesRegister', 'ns SalesRegister'[InternalId], "myAvg", AVERAGE('ns SalesRegister'[Sales/Unit])), [myAvg] * 'ns vwInventorySnapshot'[OutofStock])

[NBOnecall]

I'm getting an error with the ns vwInventorySnapshot'[OutofStock] piece. Looks like it is not an available option for the SUMX formula.

[masonlee01]

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]

[NBOnecall]

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.

[masonlee01]

What is it summing too? The average of all prices times the sum of all out of stock instead of the individual lost sales?

[NBOnecall]

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])

[NBOnecall]

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]

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]

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.

[NBOnecall]

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. 

[masonlee01]

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.

[masonlee01]

You'll need to adjust to your tables & columns, but I think it's working