Help trying to sum a measure

[NBOnecall]

Hi,

I have the following equation for a measure -

PosLossSalesAll = 

CALCULATE(sum('ns InventorySnapshot Group by SKU'[ThirdOptionFirstDate]))
* 
DIVIDE(sum('Demand 2 1 18 - 6 23 19'[Quantity]),CALCULATE(COUNTROWS('ns InventorySnapshot Group by SKU'),FILTER('ns InventorySnapshot Group by SKU','ns InventorySnapshot Group by SKU'[OnHand]<>0)))
*
(var itemInternalIDAmount = SELECTEDVALUE('ns InventorySnapshot Group by SKU'[ItemInternalId])
Return
calculate(AVERAGEx('Demand 2 1 18 - 6 23 19','Demand 2 1 18 - 6 23 19'[Amount]),
FILTER( ALL('Demand 2 1 18 - 6 23 19'[Internal ID_1]), 'Demand 2 1 18 - 6 23 19'[Internal ID_1] = itemInternalIDAmount))
/
var itemInternalIDQTY = SELECTEDVALUE('ns InventorySnapshot Group by SKU'[ItemInternalId])
Return
calculate(AVERAGEx('Demand 2 1 18 - 6 23 19','Demand 2 1 18 - 6 23 19'[Quantity]),
FILTER( ALL('Demand 2 1 18 - 6 23 19'[Internal ID_1]), 'Demand 2 1 18 - 6 23 19'[Internal ID_1] = itemInternalIDQTY)))

It is saying that Possible lost sales = Days Out of Stock * Demand Per Day in Stock * AvgSale

I am getting the correct result per row, but it doesn't sum at the total line of the table visulization.

Any help would be apperciated. I have searched on this forum for a bit now and can't find a use case that helps me.

Thanks,

Noel

[Cmcmahan]

Ahhh, yes.  Trying to fight the default sums in a table/matrix.  You can read about my adventures fighting this particular feature of Power BI here.  Long story short, PowerBI doesn't sum measures in the total section of a table.  It re-calculates the measure, but with the context of the entire section instead of the individual row.

So because your measure is so complicated, I'm not even going to attempt to re-write it.  The problem is that you're using SELECTEDVALUE and when totalling, there are multiple values so you get a blank instead of a sum. You may be able to simplify your original measure to not specify the filter context, instead letting the table set up the context for you.  I'm not sure how your rows are grouped, but you essentially have to do something like:

PossibleLostSalesWithTotal = IF(ISFILTERED(<Column you group rows by>), [PosLossSalesAll], <Calculate entire total here>)

It's not pretty, and will likely duplicate a lot of your measure, but without being more familiar with your data, that's the best I've got.

[NBOnecall]

I have two tables, one for Demand and one for the Inventory Levels on a ceratin day. The Demand table show number of sales per day for the specific item (ItemInternalID). It is a lot of calculated columns and measures to get me to my Possible lost sales = Days Out of Stock * Demand Per Day in Stock * AvgSale as you can see, but even getting to that point was difficult as well. I guess I don't understand the

<Calculate entire total here>

part of your equation. I am guessing that it is the total Possible Lost Sales somehow calculated without me using my above equation? But I don't see a way of getting there without using my equation.

Thanks,

Noel

[Cmcmahan]

You can use the above equation as a starting point, but can't just copy/paste it.

PowerBI is re-evaulating that measure for the total, but in a context where there are multiple ItemInteralId's.  Your measure uses SELECTEDVALUE to get the current [ItemInternalId], but returns a blank when there are multiple IDs available like in the total row.

I haven't really messed with your data, so I don't know how close this will be to working, but you might try editing your original measure like this:

PosLossSalesAll2 = 

SUM('ns InventorySnapshot Group by SKU'[ThirdOptionFirstDate])
* 
DIVIDE(sum('Demand 2 1 18 - 6 23 19'[Quantity]),CALCULATE(COUNTROWS('ns InventorySnapshot Group by SKU'),FILTER('ns InventorySnapshot Group by SKU','ns InventorySnapshot Group by SKU'[OnHand]<>0)))
*
AVERAGE('Demand 2 1 18 - 6 23 19'[Amount])
/
AVERAGE('Demand 2 1 18 - 6 23 19'[Quantity])
)

Before, you were going through and explicitly defining filters and context, when the table does that for you.  I just removed anything that filtered based on ItemInternalId because the table visualization is doing that for you.  This has the added benefit of being much easier to read and edit.

[NBOnecall]

I understand I just can''t copy and paste.

The measure you gave got me the same exact row by row answer in the table. It also got me a total at the bottom, unfortunately that subtotal was not the same value as when I export that data to excel and sum.

[Cmcmahan]

How far off is it from the Excel sum?  There might be rounding precision differences with the division in the measure.

[NBOnecall]

It is a lot, when summing in Excel from the export of data I get, a little of 4 million, and in Power BI I see 2.8 million.

[Cmcmahan]

Hrm. So clearly there's math errors somewhere.

My guess is that the issue is with the AVERAGEs.  The average of a whole set is not the same as summing averages of subsets.  

To fix this, I would try to figure out what equation would get the correct AvgSale when applied to the entire dataset.  The only way I can think of to make this happen is to calculate each average individually:

Here's how you can do that as a measure:

AverageQtyByItemId = SUMX(
    SUMMARIZE('Demand 2 1 18 - 6 23 19', 
        'Demand 2 1 18 - 6 23 19'[Internal ID_1], 
        "QuantityAvg", AVERAGE('Demand 2 1 18 - 6 23 19'[Quantity]) 
    ),
    [QuantityAvg] 
)

Do that for each of your averages, and then you can edit the original measure:

PosLossSalesAll3 = 

SUM('ns InventorySnapshot Group by SKU'[ThirdOptionFirstDate])
* 
DIVIDE(sum('Demand 2 1 18 - 6 23 19'[Quantity]),CALCULATE(COUNTROWS('ns InventorySnapshot Group by SKU'),FILTER('ns InventorySnapshot Group by SKU','ns InventorySnapshot Group by SKU'[OnHand]<>0)))
*
[AverageAmtByItemId]
/
[AverageQtyByItemId]
)

This will still give the same result for each individual value, since the AverageQtyByItemId only has one row in the SUMMARIZE table to sum, and will give a proper value for the overall total, since now it calculates each of them and sums them.

You may have to do something similar for Demand Per Day in Stock, since I'm not sure if the math works out to sum the quantity and then divide by the number of rows where OnHand<>0.  

[NBOnecall]

@CmcmahanFirst I want to thank you for your help, and apperciate your time.

Second, I went and re did the two AVERAGE equations, and then threw that into the PosLossSalesAll3 measure. The reason I believe I went with the following code for AVGSaleAll is that doing it your way, or the way I had done it in the past I didn't get unqiue answer per Item ID.

AVGSaleAll = var itemInternalIDAmount = SELECTEDVALUE('ns InventorySnapshot Group by SKU'[ItemInternalId])
Return
calculate(AVERAGEx('Demand 2 1 18 - 6 23 19','Demand 2 1 18 - 6 23 19'[Amount]),
FILTER( ALL('Demand 2 1 18 - 6 23 19'[Internal ID_1]), 'Demand 2 1 18 - 6 23 19'[Internal ID_1] = itemInternalIDAmount))
/
var itemInternalIDQTY = SELECTEDVALUE('ns InventorySnapshot Group by SKU'[ItemInternalId])
Return
calculate(AVERAGEx('Demand 2 1 18 - 6 23 19','Demand 2 1 18 - 6 23 19'[Quantity]),
FILTER( ALL('Demand 2 1 18 - 6 23 19'[Internal ID_1]), 'Demand 2 1 18 - 6 23 19'[Internal ID_1] = itemInternalIDQTY))

Please take a look at the output with your suggested Measures. I am a noivce when it comes to Power BI, so I am not sure what I am missing.

[NBOnecall]

So I went through and moved a realtionship from a third table (it brought in the name of the product and description). This fixed the same results for each row. But, now I am back to having the sum be calculated inccorectly. I can see that it is calculating 420.89*3,906*.067 = 110,624.31, but why isn't it matching the total of all "PosLossSalesAll3"?