Forum Discussion
Summary Error with IF Statement
Hello,
I'm having issues with an IF and/or SUMX formula. I essentially need the calculation done at every line level & then aggregated. With my first attempt using IF, the results are correct at the line level but don't sum up properly. It should show $21K, not $35K.
I assume I really need to be using SUMX, but that doesn't seem to be calcing at the line level correctly. Can anyone help pinpoint what I'm doing wrong? Here are my measures:
08.a Expired Release via Scrap = IF([01. Expired PQ] < [07. Gross Scrap] , [01. Expired PQ] , [07. Gross Scrap] ) * -1
08.a2 Expired Release via Scrap = CALCULATE(SUMX(EEORE, IF([01. Expired PQ] < [07. Gross Scrap] , [01. Expired PQ] , [07. Gross Scrap]) * -1 ))
Thanks!
J
11 Replies
Hi aminw0u
Are the lines in your matrix an exact match to the lines in your source data? Or are these aggregated values?
They are aggregated on some level. I have a data set with 34 columns & I only pulled 7 of those into my filter to drill down through before I got to what you see here.
So for SKU Name 87 you have a values of $71 and $88
Are these values based on multiple rows that have been aggretated to this single line?
Hi aminw0u,
According to your descriptions above, you may need to firstly summarize your data at [Generic SKU Name] level, then use SUMX to get the correct total in your scenario. The formula below is for your reference. :smileyhappy:
08.a Expired Release via Scrap = SUMX ( SUMMARIZE ( EEORE, EEORE[Generic SKU Name], "abc", IF ( [01. Expired PQ] < [07. Gross Scrap], [01. Expired PQ], [07. Gross Scrap] ) * -1 ), [abc] )Regards
I think it makes sense to summarize, but when I do that, it gives me an answer that is even more incorrect than before unfortunately. :smileysad: The 08.3 column is the one that includes the summarize formula.
08.e Expired Release via Scrap =
SUMX (
SUMMARIZE (
EEORE,
EEORE[Generic SKU Name],
"abc", IF ( [01. Expired PQ] < [07. Gross Scrap], [01. Expired PQ], [07. Gross Scrap] )
* -1
),
[abc]
)
