8 Replies Latest reply on Jan 10, 2012 10:47 AM by Shawn Wallwork

# Total for month

How do I display the total accross all values for a specific month?  I know that I can create a calculated field like "TOTAL(SUM([Users]))" and it displays the total for all of the data in the chart.  However, I want to be able to display the total for just the month that is being displayed in a bar chart, or the month the user is looking at in a line chart across time.  The end goal is to be able to display the count for the filtered data point for the time period, the total for all data points for that time period, and the percent of the total that data point is.  I know that this should be simple but for some reason I am not able to get the correct values for the total.

• ###### 1. Re: Total for month

I'm attaching a workbook to show what I am working with and what I need to add this information to.  For all charts in this workbook I want to have the Total, and Percent of Total showing in the mouse over details.  I want the totals to be just for that period in time (week/month) and I can't seem to figure out how to do that.  I do know that if I do the Column grand total for the bar charts that the information is correct but it displays at the bottom of the chart and for all months/weeks instead of just the one I am displaying in the chart.  I would think there has to be a way to do a calculation that does the same total, except for just one month/week so that I can then display that information and add another calculated field to create the percentage based on it.  Any and all help is greatly appreciated.

• ###### 2. Re: Total for month

Tim, since none of the experts are jumping in, I'll take a shot. If I understand correctly you want the total for each individual bar chart to appear in the Tooltips. If you put this:

WINDOW_SUM(SUM([Account Activations]) )

in a calculated field and then put it on the level of detail shelf, it will show up in tool tips when you hover over the chart. Hope this helps.

• ###### 3. Re: Total for month

Yes, I ended up doing that for the bar charts yesterday and it did work.  I actually had WINDOW_SUM(TOTAL(SUM())) but that's a bit repetitive and not necessary so I removed the TOTAL.  That does give me part of what I want with the Total for the bar charts.  I still need to get the percent of that total to be something in the level of detail as well as do the same for each month/week in the line charts.  I'm working on a good solution to those now but don't have it yet.

• ###### 4. Re: Total for month

The solution for the percent of the total is just another simple calculated field of

SUM([Account Activations]) / WINDOW_SUM(SUM([Account Activations]))

So that takes care of the bar charts that are isolated to one month/week.  Now for the more complicated matter of calculating these numbers in a line chart that spans a period of time.

• ###### 5. Re: Total for month

For anyone who is curious I was able to get the values working correctly for the line chart.  I created a calculated field Total For Month/Week that was TOTAL(SUM[Account Activations]) that is calculated by Cell.  Then I created another calculated field Percent of Total For Month/Week that was SUM([Account Activations])/[Total for Month/Week]

• ###### 6. Re: Total for month

Tim, congrates! Have you got a screen shot or better yet a workbook, I'd like to see how you put it all together. --Shawn

• ###### 7. Re: Total for month

Sure Shawn.  I've attached an updated version of the workbook I posted earlier with all of my changes.

• ###### 8. Re: Total for month

Tim thanks for sharing. I'll have a look. --Shawn