Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I
Helper I

Keeping criteria test on the most granular level in a SUMX

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  =
FactPhysicalInventory[Debit/Credit]="S" ||
FactPhysicalInventory[Movement Type] In {"701","702","703","704","707","708"} ||
FactPhysicalInventory[Quantity Counted]< FactPhysicalInventory[Book Value],
FactPhysicalInventory[Difference Amount],0

Helper I
Helper I

Thanks David. Looking into this. -John

Community Champion
Community Champion

Hi @Datazen -


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


Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors