# Dividing Two Running Total Columns in Power Pivot



## MCTampa (Dec 27, 2017)

I am trying to create a value called Average Weekly Rate (AWR) which is the calculated by dividing the running total of Revenue by the running total of Volume.

I have Power Pivot and actually did this successfully in August, but the file got corrupted and now I'm back to square one.
Here are the parameters I'm working with:

My data is in Access.
The data needs to go into a pivot table to be able to create booking curves based on various criteria.
All of my data is based on a field called Comp Date. 

In a normal pivot table, I would have my Comp Date in my rows, and Revenue and Volume in my values with them being displayed as a running total in Comp Date.  

My thought was that I could create a running total for Revenue and Volume in Power Pivot and just divide the two, but I cannot get the running total to work.

The formula I am using (for volume as an example) is:  
Volume =calculate(sum(Output[SumOfRevenue Transaction Volume]),filter(all(Output[Comp Date]),Output[Comp Date]<=max(Output[Comp Date])))

However this is just not working and I'm at a loss as to what is going on.  When I bring volume into my pivot table, I am not getting a running total, just daily totals.  I can only get the running total when I make it display as such and that does not allow me to accurately create my AWR.

Thanks for any assistance,
Mike


----------



## MCTampa (Dec 27, 2017)

EDIT:  Solved SOME of it ==>

When I changed
Volume =calculate(sum(Output[SumOfRevenue Transaction Volume]),filter(all(Output*[Comp Date]*),Output[Comp Date]<=max(Output[Comp Date])))

Needed to be this:

Volume =calculate(sum(Output[SumOfRevenue Transaction Volume]),filter(all(*Output*),Output[Comp Date]<=max(Output[Comp Date])))

However my running totals for volume were collecting the data for ALL YEARS, not specific to their check in year.

Looking at a normal pivot table, this is what I want - just with the ability to divide the revenue by the volume.
I think the answer you guys will come up with is that this needs to be done in the data, but I can't do it that way because I can't really order my data in any sort of a sequence.  I really need to be able to do this directly in the pivot table itself.


2018
2017
2016
2015
Row Labels
Vol
Revenue
Vol
Revenue
Vol
Revenue
Vol
Revenue
2/11/2016
2
             2,648
                      -
1
             1,189
3
             5,303
2/12/2016
5
             4,010
1
             1,294
2
             2,236
4
             6,377
2/13/2016
6
             4,239
2
             2,270
3
             3,980
4
             6,377
2/14/2016
14
           10,679
2
             2,270
4
             5,006
5
             7,651
2/15/2016
15
           12,003
2
             2,270
9
             8,513
6
             8,608
2/16/2016
17
           13,206
5
             4,679
10
             9,292
6
             8,608
2/17/2016
28
           17,530
4
             4,138
12
           10,833
9
           11,782
2/18/2016
28
           17,530
10
             8,254
12
           10,833
9
           11,782
2/19/2016
30
           19,158
10
             9,764
15
           12,957
13
           18,951
2/21/2016
32
           20,436
11
           10,713
16
           13,256
15
           22,307
2/22/2016
36
           27,012
16
           16,715
17
           14,282
17
           24,042
2/23/2016
38
           28,010
16
           16,715
21
           17,042
18
           24,549
2/24/2016
40
           29,802
17
           18,679
28
           24,560
18
           24,549
2/25/2016
46
           37,216
18
           19,926
29
           25,699
18
           24,549
2/26/2016
47
           38,065
20
           21,144
29
           25,699
19
           25,030
2/27/2016
51
           39,251
23
           25,306
30
           26,700
21
           27,128
2/28/2016
57
           43,283
28
           28,642
30
           26,700
21
           27,128
2/29/2016
61
           45,814
28
           28,642
33
           29,006
22
           27,614
3/1/2016
66
           48,299
31
           31,421
37
           31,490
24
           29,249


<tbody>

</tbody>


----------



## gazpage (Dec 28, 2017)

You may save yourself some stress if you just look up the TOTALYTD function. Create two measures using that, one for sales and one for volume, and then a third which is one divided by the other.


----------



## gazpage (Dec 28, 2017)

https://www.daxpatterns.com/time-patterns/

This shows the first principles way of doing YTD. Still create two measures and divide them though.


----------



## MCTampa (Dec 28, 2017)

I'm not sure this is how I want to go about it.
I've already created my data in Access.
Because I'm building a booking curve what I want to do is have everything laid out in a date-equivalent manner.
For example:  because we are in the year 2017 (for a few more days) I want the x-axis of my chart to reference 2017 dates.  For bookings made in 2016, I adjust the booking date to a "comp" date which allows me to equate it to a day in 2017.

In the table above, I scrolled through my data to capture a section which had sufficient information for display purposes.
In the above table I am showing the following:

Bookings made on a specific day as noted on the side, for occupancy in a specific year, as noted across the top.
Bookings made in 2016 for 2017 occupancy showing their actual 2016 booking date.  Since 2017 is the current year, this serves as the basis for my model.  (I use a Year, Year-1, Year-2 and Year+1 approach, so it's not based on the actual numerical year).
I also show bookings made in 2015 for 2016 occupancy based on a 2016 comp date as well as bookings made in 2014 for 2015 occupancy based on the same 2016 equivalent date.  Lastly I also have bookings made in 2017 for 2018 occupancy, again adjusted to a 2016 date so everything lines up.

What governs my data is the current year (2017) and all activity into that year.  Activity into prior or future years is always 'adjusted' or 'comped' to a date which allows everything to be charted on one graph.

Since I have already built this into my data in Access, I do not think the approach you are showing me will work.
Can I send you my data so you might better understand what I'm working with?


----------



## gazpage (Dec 28, 2017)

Happy to look at it.  I think you need to explain mechanically what you need. Ie “a table with the months down the side and then a column showing the YTD sum of sales divided by YTD volumes”.


----------

