Under the heading of “Great Functions I Never Knew,” is the AGGREGATE function. While it’s useful at times that totals added to a Table Style formatted range always reflect the filtering (total reflects visible rows), sometimes you also need the total that includes all rows.
The AGGREGATE function lets you specify a code for the function, such as 9 for SUM, and a code for what values the function should ignore (4 ignores nothing). Then, specify the range. So for the table range called Table1[Qtr], the formula would be=AGGREGATE(9,4,Table1[Qtr 1]).