I will like to calculate the total excess column using the following condition
if sum of currentstock - forecast > 0, excess is currentstock - forecast, else its zero.
As u can see the sum of excess is 85 which is less than the forecast, i want to compare their totals and if excess is less than forecast, i should get a zero as the forecast will eat up the excess.
if the total excess is bigger than the forecast, then the excess with multiply the corresponding cost to give excess dollar. I know its a lot but I will really appreciate your input.
is this what you are looking for? If yes, then add straight table with following dimensions and expression accordingly.
= IF(Sum(TOTAL <Product> CurrentStock) > Sum(TOTAL <Product> Forecast), 0, Sum(TOTAL <StoreLocation> CurrentStock) - Sum(TOTAL <Product> Forecast))
= Num(fabs(Column(1) * Sum(Cost)), '$'&'(#,##0.00)')
Change the labels for expressions accordingly.
Can you try this for excess? If not can you send us the screen shot where you can see the 0's coming
= IF(Sum(TOTAL <Product> CurrentStock) > Sum(TOTAL <Product> Forecast), 0, Sum(TOTAL <StoreLocation, Product> CurrentStock) - Sum(TOTAL <Product> Forecast))
you have lot of other dimensions here we have to look into your actual data set to come up with correct expression. Based on what you sent in your excel it works but if your dataset is different we might need to tweak around. Can you share your qvw file masking data. You can scramble your data using scramble technique. Check here