To add the Total Premium or Avg Premium for each year into your view you can click on Analysis -> Totals -> Add all subtotals.
You can control the way the subtotals are computed (sum, avg, etc.) by going to the same menu and clicking on 'Total all using'.
In the image below I've added subtotals (total using sum) in the first column. The second column has your totals computed using avg.
I duplicated the first sheet to produce the second column, which is floated next to the first sheet in the dashboard (see attached file).
This is just a POC, I would definitely recommend playing with it and formatting it to be more user-friendly. You could also take a different approach and compute the sum/avg for the Premium metric and use that value however you want in other visuals, if you didn't need all the other values in this table.
Hope that helps!
Book2.twbx 33.6 KB
Thanks Elliot. That worked for me. Now what I'm trying to get to is a waterfall chart from this information and walk prior year premium to current year premium. And I'm not really sure how to set that up.
How can I walk total premium from last year to this year with these up/downs (I guess first off how can I get a metric that sums current year minus prior for premium, average premium, and volume change)
I want these on my waterfall:
Volume Variance: (2016 units-2015 units)*Overall Premium average 2015
Price Variance: Sum of (2016 rate-2015 rate)*2016 volume for BOTH channels
Mix Variance: This is a little complicated.....For each channel, compute mix of total volume in 2015. Multiply that mix percentage by total 2016 volume for each channel to get the adjusted number for each channel. Subtract 2015 channel price from total average price. Multiply these numbers by the difference in current volume minus adjusted mix volume. Add those up for mix impact. I have no idea how to set this up
How can I set this up? Are there any good resources for sample visualizations. I imagine others are doing this. Thanks!!