Problems with PivotTable Running Totals and HOW TO FIX THEM

while excel has some built-in functionality for calculating running totals and pivot tables it has some limitations in this tutorial i'll show you what they are and how to get around them with a power pivot formula that you can also use in power bi in this tutorial we'll look at how to create pivot table running totals that allow you to see the aggregation at the month quarter and year levels enabling you to create charts like this i'll be using three data tables that contain the data for actuals forecast and budget by date i'll insert a regular pivot table let's pop it on this existing worksheet over here and i want to see the data by date and the sum of the actuals let's just expand this we'll expand the entire field and i'll change the design so that the subtotals are at the bottom of the group let's apply some number formatting here so it's easier to read now i can use the show values as setting by right clicking show values as and then running total in now here i have to choose what level i want the running total at i'll choose years and then let's add in the actuals again again i'll set some number formatting so that they're consistent this one contains my running total so i'll rename that and we'll get rid of the two beside the actual now you can see the quarter level of the data doesn't show the running total correctly for 2019 and the quarter level for 2020 actually adds the q1 2019 and q1 2020 figures together and so on for each quarter now i doubt that's what most people want to see this show values as running total setting is also available for power pivot pivot tables and it works in the same way now the work around if you want to see a running total in a more logical order where each quarter adds to the previous to give us the year total and it builds from there is to use powerpivot and write a measure to calculate the running total let's take a look i've already loaded these three tables into the powerpivot model let's open it up on the powerpivot tab we can go in and manage the power pivot model and if we look at the diagram view you can see the tables at the bottom i've also added a table for my calendar this is a dimension table and it's going to enable me to create relationships between my three tables that contain the data i want to summarize these are known as my fact tables and the calendar table which contains the dimensions that is the fields that i want to summarize the data by in this case i want to summarize it by year month and quarter so let's create the relationships we just left click and drag the common date fields to one another from each table up to the calendar dimension table you can see the connector lines indicating that the relationships are set up so i'll close the power pivot window and next i need to create the measures that are going to calculate the running totals so again on the power pivot tab under measures i want to create a new measure the first one i'll create is for the actuals so it's fine assigned to the actual table we'll call it actual rt short for running total and the formula is calculate we want the sum of the actual close parentheses on sum let me control and scroll to just increase the font size in the formula field then i want to filter all selected calendar dates close parentheses on all selected that are on or after the calendar date where the maximum actual date is sorted in descending order close parentheses three times let's just check the formula no errors that's good we'll set some number formatting and click ok now i can insert a pivot table from the data model pop it in that cell there that's already selected now it's very important that the date fields come from the calendar dimension table so remember i want it summarized by year quarter and then month and the values that i want summarized are those calculated by the measure that i just wrote you can tell it's a measure because it has the function icon before the measure name let's just collapse the quarters collapse the entire field it's easier to see then we can see the running total now correctly adds from one quarter to the next and if we put the subtotals at the bottom we can collapse it even further so that we only see the year so it doesn't matter which level we look at this pivot table at the running totals display as you'd expect them to with one period building on the next let's create the next measure to make it quicker we'll just create a new measure and this time i'm going to paste it in so it'll be called forecast rt there it is there you can see it's summing the forecast and finding the maximum of the forecast date let's check the formula set the formatting and click ok it's automatically added to the pivot table let's do that again this time for the budget and then we can visualize the data in a chart set the number formatting and we're done okay now we have our three measures you can see they all calculate the running total so let's go ahead and insert a pivot chart i'm going to go with the area chart and then i'll just bring it up here we'll hide some of these field buttons you don't need those i want to keep the drill down or expand and collapse buttons though so i need to turn these off one by one let's put the legend at the top and what i want to do is change the style of the actual series so that it's a line rather than an area so with the chart selected i'm going to right click change series chart type and then for the actual i want a line click ok now to stop the line dipping down i want to right click the chart and go in and select data and in here with the actual rt hidden and empty cells i want to have a gap that's just going to stop that line dipping down to the bottom okay and now all i need to do is set my color formatting so with any of the chart selected i'm going to ctrl 1 to open the formatting pane and in here i can change the colors so for example i might want the budget fill to be dark blue the forecast to be a lighter blue and to make my actual stand out i'm going to set this line to pink color and now when i expand or collapse the fields in the chart you can see it automatically updates and correctly shows the running total so you can make this a bit wider so that the months on the horizontal axis have space so there you have a measure for running totals in powerpivot keep in mind that this same measure can also be used in power bi to create running totals for visuals i hope you found this formula useful you can download the file for this lesson from the link here and if you like this video please give it the thumbs up and subscribe to our channel for more and why not share it with your friends who might also find it useful thanks for watching

Channel: MyOnlineTrainingHub

Views: 12,787

Rating: 4.9881129 out of 5

Keywords: pivottable running totals, power pivot running total

Id: 9VeAmodOvMc

Length: 8min 12sec (492 seconds)

Published: Thu Jul 15 2021