I’m trying to use this method for a Part Number context (rather than date) and using YTD numbers to accomplish the date portion. Unfortunately the total that the following delivers bears no similarity to the context table. The variables are working through some criteria so that the price change is only evaluated if it passes those criteria. Can you spot what I’m doing wrong?
Would appreciate any guidance you can offer!
Price Impact: Sell Price Change $ = VAR TooBigTest = IF([% Change in Quantity] < 4, "Pass", "Fail") VAR TooSmallTest = IF([% Change in Quantity] > -0.75, "Pass", "Fail") VAR SellPriceChange = SWITCH( TRUE(), [Quantity YTD] = 0, 0, [PYTD Quantity] = 0, 0, TooBigTest = "Fail", 0, TooSmallTest = "Fail", 0, ([Avg Sell Price $ (Part)] - [PYTD Avg Sell Price $ (Part)]) * [Quantity YTD] ) VAR TotalSellPriceChange = SUMMARIZE(Sales, Sales[Part#/Item#], "SellPriceChg", SellPriceChange) RETURN IF( HASONEVALUE(Sales[Part#/Item#]), SellPriceChange, SUMX(TotalSellPriceChange, [SellPriceChg]) )
The results table I’m seeing is as follows; although this result shows 0 it is not always zero depending on the filters applied: