I need support on a running sum calculation that is partition on month (MY) and a product number field. I am trying to take a running sum calculation and minus the current month for each occurring month.. Each time I place the calculation on the Measure pane, the calculation will change all of my other RUNNING_SUM calculations, EXCEPT the measure that is pulled in directly from the MEASURE shelf. I have four other calculations that are also dervived from a basic SUMIF/SUMPRODUCT excel function. I am able to get all other partitioned calculatios to function correctly. Any thoughts, I spent a lot of time looking at many of the post from the past three (3) years; which were great, now I need some help..
Cost from the measure shelf
1a.Find the percent of each row cost and multiply by the total cost
SUM([01.Costs ])/TOTAL(SUM([01.Costs ])) --- Totals summarize values from the Table (Across)
1b. Cost % (1a) minus a couple dimensions
2a. Sales multiplied by Cost% (1b)
2b. MAX([Sales])*[01b.Cost% )]) ---- each sale is rolled up to only 1 product number, which is why I find the % of each cost so I can multiply by the MAX sales to get the allocated sales.
3a. YTD - Running Sum Cost
RUNNING_SUM(SUM([01. Costs ]))
3b YTD - Running Sum Sales
4a. ERROR occurs
//This is a running_sum or previous_value calculation to return the YTD minus the current YTD.
[03b.Alloc Sales] - LOOKUP([03b. Alloc Sales],-1)
4b. Sames as 4a. used to allow 4a. to occur and then minus the current YTD.
[04a.CP - Sales]-[03b.LTD Alloc Sales]
5. Cost Margin
[02b.Allocated Sales]-(SUM([01. Costs ]))
6. Cost Margin %
[05.CM ]/[02b.Allocated Sales]
I have attached two screen captures -- one with Calculation 1 - 3, 5 and 6 working. And the other screen capture show what happen when I place calcualtion 4 on the view. I am partioning and addressing the same (Customer Name, Month/Year and Product Number as my partion and addressing the other dimension fileds . I left Sort to automatic, At level = deepest and restarting every = None.
I hope someone can assist me.