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

    Total for month

    Tim Hill

      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
          Tim Hill

          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
            Shawn Wallwork

            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
              Tim Hill

              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
                Tim Hill

                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
                  Tim Hill

                  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
                    Shawn Wallwork

                    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
                      Tim Hill

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

                      • 8. Re: Total for month
                        Shawn Wallwork

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