Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
)
)
Thanks David. Looking into this. -John
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
46 | |
31 | |
30 | |
18 | |
18 |