Keeping criteria test on the most granular level in a SUMX

[Anonymous]

I'm having a problem keeping the evaluation of a SUMX expression at the most granular level and only on that level.

Each record in my table represents an individual product. I want to calulate a measure named 'Neg. Diff Val' based up a test of whether that INDIVIDUAL PRODUCT has a 'Quantity Counted' < 'Book Value'.  (This is the last condition in my IF statement. The other 2 conditions are just routine ones.)

If the condition is true I want 'FactPhysicalInventory[Difference Amount]' returrned to the SUMX formula. If it isn't true I want zero returned. The expression works fine when the data is displayed on this granular level in Power BI. The problem comes when I display the data at a higher level in Power BI such as summarizing it by Document. (Where it is summarizing multiple lines.) This is because it is then evaluating 'Quantity Counted' < 'Book Value' on an AGGREGATE level and testing 'Quantity Counted' < 'Book Value' for the ENTIRE DOCUMENT. This returns an incorrect result and is not summing an accurate calculation based upon each individual product. 

I thought the below code would accomplish since SUMX is an interative function. But it isn't doing that. How do I get this to evaluate the test for 'Quantity Counted' < 'Book Value' on the most granluar level of each individual product?    

Neg. Diff Val  =
Sumx(
FactPhysicalInventory,
if(
FactPhysicalInventory[Debit/Credit]="S" ||
FactPhysicalInventory[Movement Type] In {"701","702","703","704","707","708"} ||
FactPhysicalInventory[Quantity Counted]< FactPhysicalInventory[Book Value],
FactPhysicalInventory[Difference Amount],0
)
)

[Anonymous]

Thanks David. Looking into this. -John

[dedelman_clng]

Hi @Anonymous -

What is your expected result at the higher grouping levels? Usually for this kind of measure you need to use IF(HASONEVALUE(Product[Product]), ... ) as a wrapper around your logic.  This way you can control how it calculates at each level.

If this doesn't make sense, please share some sample data and expected results, or a sample pbix file with any sensitive data removed.

Hope this helps

David