1 2 Previous Next 15 Replies Latest reply on May 2, 2014 4:59 PM by Vish Pattanashetty

    Need subtotal on my report

    Vish Pattanashetty

      I am fairly new here. Can someone help me put subtotals and totals on my report? I have attached my Tableau workbook and also my source excel files. The subtotal and total I need are in excel (columns J,P,R,S in Results tab)

        • 1. Re: Need subtotal on my report
          peter.ugincius

          I wasn't able to download your file.  Not sure if you are aware of the following....

          • 2. Re: Need subtotal on my report
            Vish Pattanashetty

            Thanks for the reply. Yes, I tried that. It does not work in my situation. What error message does it give you when you try to download the file. I just tried it and I am able to download it.

            • 3. Re: Need subtotal on my report
              Matt Lutton

              Study the 3 part series here, and you'll know pretty much everything there is to know about Subtotals and Grand Totals in Tableau:

               

              http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-1/

               

              Cheers!

              • 4. Re: Need subtotal on my report
                Vish Pattanashetty

                Yes, that is the link I spent yesterday and today going through. I created the formula based on their recomendation. It did not work. If you look at field GTS in my workbook, I have used that formula (from second page) for that field

                • 5. Re: Need subtotal on my report
                  Patrick A Van Der Hyde

                  Hello Vish Pattanashetty,

                   

                  I looked through the attached workbook and see that all of the measure values have been placed on the rows shelf as Discrete values.  Totals will not function for Discrete fields placed on the Rows shelf. The values need to be continuous or at least on the Text label.  Since there are multiple fields in use, the only option I know of is to utilize the Measure values shelf.

                   

                  I have updated the originally supplied workbook with the one attached.  I modified the formatting of the banding of the Grand Totals line to make them similar to the original and for the Grand Totals line to stand out.

                   

                  A great resource regarding Discrete (Blue) vs Continuous (Green) is here: Blue things and Green things - The Information Lab

                   

                  --  Patrick

                  • 6. Re: Need subtotal on my report
                    Vish Pattanashetty

                    Patrick,

                     

                    Thank you so much for that answer! We are getting closer. If you look at the total for Billable rev, expected rev and margin $$, the total at the bottom does not add up to the summation of the columns. Can you help?

                     

                    Again Thank you so much!

                     

                    Vish

                    • 7. Re: Need subtotal on my report
                      Patrick A Van Der Hyde

                      Vish Pattanashetty,

                       

                      I believe you reviewed the information supplied here - Why Your Grand Total or Subtotal Isn't Working as Expected by Jonathan Drummey.  The key thing to remember with Totals is that they are computed as if none of those Dimensions on the Rows shelf exist in the view.  So, if the desired output is to show a Window_Sum() of the values in those columns such as [Billable Rev] then we need to use the approach that is outlined here: http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-2/

                       

                      However, once we add a copy of the Campaign field to the level of Detail for use by our Grand Total calculation, we will need to update all of the measure values in use in the view to utilize this level of detail as well.  The attached workbook utilizes new versions of each measure value to achieve the end goal as Jonathan describes on his article.

                       

                      I would also encourage you to vote for the feature idea represented here: http://community.tableau.com/ideas/1232 to allow for totals/subtotals to be computed based on the displayed marks (table calculation).

                       

                      I hope this helps.

                       

                      Patrick

                      • 8. Re: Need subtotal on my report
                        Vish Pattanashetty

                        Patrick,

                         

                        Thanks. did you by chance upload the previous file? The numbers look the same. Also if I were to do subtotal by field "Type" for those values, would it be by this new formula you are creating? Can you please re upload the file with the total and subtotal? that would be so useful! Thank you so much

                         

                        vish

                        • 9. Re: Need subtotal on my report
                          Patrick A Van Der Hyde

                          Vish  - the subtotals won't work in this case. 

                           

                          If subtotals are required, I suggest building separate views such as one for Type CTA and one for type Type 2 and then just a Grand total view (each Type has been set to "hidden" by right clicking and selecting "Hide") and putting them together in a dashboard.  Example is seen attached Dashboard.  Someone else may have a better way to mangle these together into a common view but this is a pretty quick way to build the desired output as a one time report.

                           

                          Beware that any filters applied or additional [Type] values would require new views to be created and added to this dashboard and filters applied to those views as well. 

                           

                          Patrick

                          • 10. Re: Need subtotal on my report
                            Vish Pattanashetty

                            Excellent! Thank you Patrick! This gets me so much closer to my goal. There are a couple of things I still do not get.

                             

                            1) the percentage totals at the bottom are more than 100% looks like they are summing up the percentages. They need to do the same calculations as the individual percentages. Can you help me with that? (I tried doing it, but could not figure out where you had done them.

                             

                            2) I tried moving the field "Billing Source" to the end. I could not do it.

                             

                            3) How do you make the field "Type" go sideways? I used to be able to do it by going to Analysis/Table Layout/advanced/and changing the number of row labels and column labels, but I am not able to do it now

                             

                            4) I tried to change the format of all the measure values to central allign but could not make it happen. I tried all types (pane, header, total and grand total. Not sure what is wrong)

                             

                            Thanks a lot for your help Patrick!

                            • 11. Re: Need subtotal on my report
                              Patrick A Van Der Hyde

                              1) so every measure in use would need to have a table calc equivalent to be utilized for percentages.

                               

                              One example - New Margin %

                              needs to become:  ([New Billable Rev]-[New sum_of_pub_payout])/[New Billable Rev]

                               

                               

                              New sum_of_pub_payout is set to:

                               

                              IF MIN([Campaign name])!= MAX([Campaign name]) THEN

                              //detail row result   

                                 SUM([IPOW Data (Ad Margin Report - IPOW.xlsx)].[sum_of_pub_payout]) ELSE

                              //Grand Total result   

                                if first()==0 then window_sum(SUM([IPOW Data (Ad Margin Report - IPOW.xlsx)].[sum_of_pub_payout])) end

                              END

                               

                              Looking at a few of the other percentage totals, this is a fair bit of work to create new versions of each measure name but this should give you the idea of what needs to be done. 

                               

                              2) try just dragging Campaign name to the second most left field.  I had no problems moving this around. 

                               

                              3) Right click on the words "Grand Total" and select Format.  From the left most pane, select "Header" tab.  Select "Default" section >Alignment and set the Direction to Sideways.

                              Make sure the Totals and Grand Totals alignments are set to Horizontal though. 

                               

                              4) this is a feature request that could be added to Ideas.  I only found the Conditional formatting request related to Measure Names/Values so maybe a new idea. 

                               

                              Patrick

                              • 12. Re: Need subtotal on my report
                                Vish Pattanashetty

                                Patrick,

                                 

                                1) I tried several times. But got nowhere on the margin %. Can you do just that and I will copy the same formula to others.

                                 

                                2) Campaign name is already the second column. I want to get "Billing Source" as the last column in the report. Looks like there is a block of measure values that are preventing it from happening.

                                 

                                3) I was able to do this. Thanks

                                 

                                4) But isn't this basic formatting? All I am asking for is that the column header and the column data be aligned. Is that not possible?

                                 

                                Thanks for your help Patrick

                                • 13. Re: Need subtotal on my report
                                  Patrick A Van Der Hyde

                                  Vish,

                                   

                                  I think I know where things may be going astray.  When working with calculations such as Rev Pricing, where nested calculations are in use combined with Table Calculations, it is necessary to define how each Table Calculation that is utilized should be computed.

                                   

                                  Open the attached workbook and from the tab 'Using Measure Values', select the field [New Rev Pacing] from the Measure Values shelf.  Left click on this field and select "Edit Table Calculation", from here, see the top of the dialog box where it says "Calculated Field" and the first value appearing is [New Billable Rev].  From here, select the down arrow and select another field.  Each of the fields used in the calculation must be set to utilize [Campaign name (copy)] as the Compute Using value.

                                   

                                  That should cover your percentage calculations.  I suggest taking the time to become familiar with Table Calculations by using some of the great resources online because these functions can be challenging to learn but once mastered, open up a whole new world of possibilities within Tableau. Jonathan Drummey has an excellent  'so you want to learn table calculations' section on his site as well. 

                                   

                                  2) - I moved Billing Source to the last column for the Dimensions.  Maybe your intent is to move it to the right of the Measure values and that is not possible without some crafty dashboard work.  I made a sample on Dashboard 2 showing a method of doing this although I'd suggest against it unless it fills some very important requirement since it will require applying all filters to both views and I think it creates confusion.

                                   

                                  4) Agreed that it should be possible but I do not know of a way and I've asked around a bit.  More access to Measure Names/Values and formatting of each as desired is a feature request.

                                   

                                  I should add for others following along that I was made aware that you were in training this week and jumped in to help answer these questions.  Usually, I'd wait for someone else to potentially provide their insight and solutions to a thread but I'm hoping the things shared in this thread are both useful to the training and to others searching for assistance with crosstab/excel sub-totals and totals issues in the future. 

                                   

                                  Glad to help and know this has been useful to you. 

                                   

                                  - Patrick 

                                  • 14. Re: Need subtotal on my report
                                    Vish Pattanashetty

                                    Yes! The Margin % is correct! Now I will try to replace others with the same formula!

                                     

                                    I will let you know how it went. Thanks for your help!

                                     

                                    Vish

                                    1 2 Previous Next