Hello from Germany, I hope you are all healthy and not too badly affected by Corona.
I've working on a problem on sub-totals and running total for many hour and could not find a solution. There's a crosstable with Week and Date, and two measures "Sales" and "Margin". I want to have a subtotal after each weeks - that works finde. And I also want to have an running total in the column right next to "Sales". Same thing to "Margin". The running total works until my first subtotal, then the subtotal adds up the accumulated values again. I've tried many ways, read many posts but could not figure it out.
Below my crosstab, cause of the different ways of counting weeks and format dates in the world, I've displayed it in an easy way for better understanding. Maybe somebody could help, it would be very very fine, thanks in advance!
Week Date Sales Margin Running-Total Sales Running-Total Margin
1 Day 01 10 5 10 5
1 Day 02 10 5 20 10
1 Day 03 10 5 30 15
1 Day 04 10 5 40 20
1 Day 05 10 5 50 25
1 Day 06 10 5 60 30
1 Day 07 10 5 70 35
ƩWeek 1 70 35 140 Correct: 70 70 Correct: 35
2 Day 08 15 10 155 80
2 Day 09 15 10 170 90
2 Day 10 15 10 185 100
2 Day 11 15 10 200 110
2 Day 12 15 10 215 120
2 Day 13 15 10 230 130
2 Day 14 15 10 245 140
ƩWeek 2 105 70 350 Correct: 175 210 Correct: 105
|
Can you try changing your subtotal to aggregate? In the properties, change Summary from Total to Aggregate. I think it should then total the first columns and not total the running-totals.