Forum Discussion
Help subtracting a measure from column
Anonymous you have to be specific which solution you tried and not worked, you have few awesome people replied to your post, be more specific.
Advocate IIII am trying to build a table like the one below:
| SalesPersonID | RegionID | Week | Baseline Sales | Sales | Variance | Running Variance |
1 | 1 | 6/27/20 | 45,000 | 45,128 | 128 | 128 |
| 1 | 1 | 7/4/20 | 45,000 | 44,872 | -128 | 0 |
| 1 | 1 | 7/11/20 | 45,000 | 46,194 | 1,194 | 1,194 |
| 1 | 2 | 6/27/20 | 45,000 | 49,220 | 4,220 | 5414 |
| 2 | 1 | 6/27/20 | 38,000 | 38158 | 158 | 158 |
I am unsure how to go about calculating the running variance. The assumption is, I can just take the baseline sales and subtract the sales and that gives us the variance, but then adding that variance doesn't work when we get to the next region or the next sales person.
I have built a summary table to calculate the baseline sales which is the average sales value in a given time frame before 5/30/20 regardless of the region.
I am able to calculate a variance, but I need to be able to calculate that running variance by salesperson. It isn't as simple as;
Running Variance = SUMX(SUMMARIZE('table1','table1'[salespersonID],