Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have pivot table that looks like the one below. I'm trying to take the difference between price and prior price (for same Lot# and plant), if increase in price, then multiply it by quantity sold at price increase. If no increase then 0. Thank you.
Rows | Measures | |||||
Lot# | Customer | Plant | Month | Price | Quantity Sold | Impact |
0021 | Johnnies | 5 | 8 | $ 4.35 | 2 | |
0021 | Johnnies | 5 | 9 | $ 5.01 | 25 | =if(5.01>4.35 for same Lot# and plant, then Impact =(5.01-4.35) *25 |
0021 | Johnnies | 5 | 10 | $ 5.10 | 21 | |
0021 | Johnnies | 5 | 11 | $ 5.10 | 23 | |
0021 | Johnnies | 5 | 12 | $ 5.25 | 10 | |
0035 | Joney | 7 | 8 | $ 2.67 | 6 | |
0035 | Joney | 7 | 9 | $ 2.80 | 4 | |
0035 | Joney | 7 | 10 | $ 2.85 | 53 | |
0035 | Joney | 7 | 11 | $ 2.90 | 5 |
I just noticed that the formula only works when price changes in same month. If month changes in the next row with a higher price for the same part, it won't calculate. ??
yes, above and Peek, not sure which method you're referring to, by default go back one row in the data. if you have more than one row per month, you'd have to summarize the data at the month level, avg or max or FirstSortedValue using -Date to get last.