Set Analysis

[Husky_Bab12]

Hi ,
The basic calculation:
((Sum(Quantity)*(partPieceWeight))+((Sum(Quantity)*(shippingUnitTareWeight))/packageQuantity))*0.453592

I'm trying to use the below expression,

=round((((sum({<$(='[CMMS '&vBOH &' BOH Days]')={">=$(vStart) <=$(vEnd)"},[Production Status]={"1"},%ReleaseSupplier = ,%Conveyance =>} Quantity)*partPieceWeight))

+
((sum({<$(='[CMMS '&vBOH &' BOH Days]')={">=$(vStart) <=$(vEnd)"},[Production Status]={1},%ReleaseSupplier = ,%Conveyance =>} Quantity)*(shippingUnitTareWeight/packageQuantity))))*0.453592)

The first part is summing the quantity then multiplying with part weight for the production status 1.

The second part is summing the quantity and then multiplying with (shippingunit/packagequantity) for the production status 1. 

The above expression works when I'm on particular part for one store.

When I'm looking for the same part with different stores it is showing - 

Can someone help me with this?

[Or]

Generally speaking, if you want your set analysis to impact the entire expression, you need the entire expression to be contained within the aggregation functions. In your current expression, this is not the case as partPieceWeight, shippingUnitTareWeight, and packageQuantity are all outside the aggregation functions, which means they are not impacted by set analysis and will also return null if there is more than one possible value or if there are no possible values for any of them.

[Husky_Bab12]

if i include them my results are overestimated. 

example for a part to two different store is including for two stores is 23 .

for  one store is 12

The approach I made similar to you said 

=(sum({<$(='[CMMS '&vBOH &' BOH Days]')={">=$(vStart) <=$(vEnd)"},[Production Status]={1},%ReleaseSupplier = ,%Conveyance =>} Quantity*partPieceWeight)) 

+
(sum({<$(='[CMMS '&vBOH &' BOH Days]')={">=$(vStart) <=$(vEnd)"},[Production Status]={1},%ReleaseSupplier = ,%Conveyance =>} (Quantity*(shippingUnitTareWeight/packageQuantity)))

showing me 46.

[Or]

Since I can't see your data or data structure, I can't actually help you with this issue - it sounds like you have a duplication / one to many situation for these fields which is causing both the duplication and the return of null without the aggregation. I'd suggest you investigate that by placing each section of your code into a different expression/measure and then figuring out which one(s) don't return a single value for your rows.

[Husky_Bab12]

Hi @Or ,

I checked with the data. It does not have any duplicates.

But I noticed with the below expression, 

=(sum({<$(='[CMMS '&vBOH &' BOH Days]')={">=$(vStart) <=$(vEnd)"},[Production Status]={"1"},%ReleaseSupplier = ,%Conveyance =>} Quantity*partPieceWeight))

when the quantity is 7371 and partpieceweight is 0.12, 

Expected value is 831

But actual is 1789. 

I want to sum the quantity and then multiply with partpieceweight. 

[Or · Accepted solution]

If the quantity is in fact 7371, and the weight is in fact 0.12, and there is no duplication, then you should get a result of 7371*0.12. If you do not, that suggests that one of the above is not correct - either there is duplication, or the weight is not actually 0.12 for all rows, or the quantity is not 7371. As I suggested before, you should try and isolate the issue by summing the quantity separately, and getting an Only(partPieceWeight) with all of the set analysis added to confirm that weight is in fact 0.12 for all cases. If both of those are the case, I'd suggest checking for cases where the weight for a specific row is null (since null wouldn't break the only().

Another alternative approach is to add all of the fields from your fact table as table dimensions, and then trying to see if there are any cases where things aren't as expected.