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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Datazen
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  =
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
)
)

2 REPLIES 2
Datazen
Helper I
Helper I

Thanks David. Looking into this. -John

dedelman_clng
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

David

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors