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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors