3 Replies Latest reply on May 12, 2017 2:33 PM by Alex Martino

# how to get totals and lines for measures

Hello. How can I add row lines after certain measures. I have price and unit by channel and tehn calculated measure of total premium. How can I get a line after the first two measures. AND also a total premium and average premium sum for each year? Spreadhseet attached.

• ###### 1. Re: how to get totals and lines for measures

Hi Alex,

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!

Elliott Stam - InterWorks

1 of 1 people found this helpful
• ###### 2. Re: how to get totals and lines for measures

PFA...Hope this Helps !

• ###### 3. Re: how to get totals and lines for measures

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!!