2 Replies Latest reply on Sep 20, 2013 1:28 PM by David Marrel

# Percent Difference of Two Running Totals

I'm trying to determine the % difference between two running totals.

My (simplified) data set:  I have a table with Date, Type (2 options:  Quota or Sales), and Amount.

What I have so far:  See the attached workbook.

Viz 1: Month to Date Quota and Sales using running totals.

Viz 2: Month to Date \$ difference using running totals i.e.  On day 1 Quota is \$100 and Sales is \$50 of the difference is \$50.  On Day 2, quota is again \$100 and Sales is \$150.  So on Day 2, the difference is \$0 (\$100+\$100 = \$50 + \$150).  I used a calculated field for this.

My trouble is with Viz 3:  Month to Date % difference suing running totals.  So for Day 1 it should be \$50/\$100 - 1 = -50%.  On Day 2 it should be (\$50+\$150)/(\$100 + \$100) - 1 = 0%.

Any ideas?  I just can't get a calculated field to work for this.  The solution doesn't have to be a calculated field and it doesn't even have to be a line graph if there's a better way to show it (probably a bar chart could work better).  But I need the % difference of running total to work.

• ###### 1. Re: Percent Difference of Two Running Totals

You might be able to get what you are looking for by creating a calculation that does separate running totals for each type and subtracts the two:

Running_SUM(sum(IF Type = 'Sales' THEN [Amount] END)) - RUNNING_SUM(sum(IF [Type] = 'Quota' THEN [Amount] END))

• ###### 2. Re: Percent Difference of Two Running Totals

That worked perfectly.  I just needed to divide your calculation by quota and I had the perfect deviation from quota.

I really need to learn my table calcs.  They seem VERY useful.