Running Total Calculation

[dgoscinski]

I have a ton of data covering a year. I have to calculate a "Defective Parts per Million." The MONTHLY formula is (#of Defects/Total Sold)*100000. Doing this in a pivot table is not a problem to calculate monthly. What I require though is a running total of the previous 12 months as well.
In other words, the yearly formula should be something like this (# of Defects in the last 12 months/Total of items sold over last 12 months)*1000000.
I can get a running total without issue in a pivot table. However, it automatically adds the month before. I would essentially like to add 11 months before.

I have included a data set WITH pivot table.
Monthly PPM is already calculated.

Here is what I need:
January 2019 PPM would be: (Total # of Defects from Feb2018 to January2019/Total Parts Sold from Feb2018 to Jan2019)*1000000
February 2019 PPM would be: (Total # of Defects from Mar2018 to Feb2019/Total Parts Sold from Mar2018 to Feb2019)*1000000
March 2019 PPM would be: (Total # of Defects from Apr2018 to Mar2019/Total Parts Sold from Apr2018 to Mar2019)*1000000
And so forth.
Basically, the bolded part of the formulas above are where I need help.

A total from previous month in the pivot table ONLY tallies from previous amount. I need the tally to ALWAYS look back over 12 months and only 12 months.

 

[Hany ali]

where's the file with explain?!!!!

 

[Nebu]

Hi:

Using Power Pivot and Data Models.

Thanks

 

[dgoscinski]

Hi:

Using Power Pivot and Data Models.

Thanks

It appears to work, but I don't see HOW you did it. I can't seem to see the formulas you used.

 

[Nebu]

@dgoscinski


Add your data to the data model, if you are using latest version of excel follow the screenshot below.



Once you add your data to the data model as above you will be taken to the following screen


Click on the "Design" Tab highlighted in the above snapshot and select the Date Table (highlighted below) and create a new calendar table,


Enter the formula for PPM & Cum PPM as below ( in the Home tab below the data as shown in the above screenshot)

PPM:=([Sum of Ext Defect]/[Sum of Qty Sold])*1000000

Cum PPM:=(CALCULATE(SUM(Table1[Ext Defect]),FILTER(ALL('Calendar'[Date]),'Calendar'[Date]<=MAX('Calendar'[Date])))/CALCULATE(SUM(Table1[Qty Sold]),FILTER(ALL('Calendar'[Date]),'Calendar'[Date]<=MAX('Calendar'[Date]))))*1000000

In the Home tab as below create pivot table clicking on the highlighted PivotTable Button.



Note:

All the above steps are a one time set up , next time once you have new data you will have to update your data tab in excel spread sheet and hit refresh all, the pivot table will update automatically.

Thanks

 

[dgoscinski]

@dgoscinski


Add your data to the data model, if you are using latest version of excel follow the screenshot below.

View attachment 64585

Once you add your data to the data model as above you will be taken to the following screen

View attachment 64587
Click on the "Design" Tab highlighted in the above snapshot and select the Date Table (highlighted below) and create a new calendar table,
View attachment 64588

Enter the formula for PPM & Cum PPM as below ( in the Home tab below the data as shown in the above screenshot)

PPM:=([Sum of Ext Defect]/[Sum of Qty Sold])*1000000

Cum PPM:=(CALCULATE(SUM(Table1[Ext Defect]),FILTER(ALL('Calendar'[Date]),'Calendar'[Date]<=MAX('Calendar'[Date])))/CALCULATE(SUM(Table1[Qty Sold]),FILTER(ALL('Calendar'[Date]),'Calendar'[Date]<=MAX('Calendar'[Date]))))*1000000

In the Home tab as below create pivot table clicking on the highlighted PivotTable Button.
View attachment 64589


Note:

All the above steps are a one time set up , next time once you have new data you will have to update your data tab in excel spread sheet and hit refresh all, the pivot table will update automatically.

Thanks

I have Excel 2016 but can't seem to find the PowerPivot Add-in. This solution, will not work for me, then.

 

[Nebu]

Hi:

Sheet 8 using power query and power query functions, there is no need to use Power Pivot. I guess Power query is a native function in Excel 2016.
Just change your base data in excel spreadsheet and refresh the pivot in sheet 8 the pivot should update with new information.

Thanks

 

[AlanSidman]

Power Query in 2016 is called Get and Transform and is located on the Data Tab.

 

[vletm]

dgoscinski
Two samples ...
a) F-column
b) H- & I- columns