7 Replies Latest reply on Feb 3, 2013 7:22 PM by Dimitri.B

    Sum underlying excel data for specific dimension values

    Paul Stephens

      How can I have tableau calculate the sum of a measure from all rows of the underlying data (not just what's displayed on a given report table)? I would like to have tableau calculate this total for a given vendor and fiscal quarter dimension combination. Next I would like to take this total and use it as a denominator in a ratio for the purpose of allocating a totally different measure (rebate, for instance which is in a totally different excel with same vendor/fiscal quarter dimensions). The goal is to take whatever revenue value data point is being displayed on a report and divide it by the fixed vendor/fiscal quarter revenue data point. The three images below should provide a good visual of my goal. Based on Q1 vend 1 sales, I would like to make a rebate calculation of $2.22 for the 2011 Q1 vend 1 sales of prod 01 to cust 1. Please keep in mind that my report may not display all records in Q1 and hence the need to have an underlying fixed calculation for the Q1/Vend1 sales. Hope I was able to successfully articulate my problem. Thanks!.

       

      Excel 1.jpgExcel 2.jpg

       

      Excel 3.jpg

        • 1. Re: Sum underlying excel data for specific dimension values
          Tracy Rodgers

          Hi Paul,

           

          I'm not sure I understand the issue. Are you able to post a sample workbook (possibly using Superstore) to demonstrate the desired outcome?

           

          -Tracy

          • 2. Re: Sum underlying excel data for specific dimension values
            Dimitri.B

            I am not sure if I understood the problem correctly, but will try to help anyway.

             

            If you want to connect to different Excel files from one sheet, then it is possible via data blending but could be complicated, depending on your data and what you want your sheet to look like. The best way to get help on this is to post mock up of the view you want to end up with and samples of your two data.

            The fact that not all records will be displayed shouldn't be a problem with data blends. If those records are filtered from one data source, that filter will not apply to the other data source.

             

            In case you were wondering if there is a way to control what is displayed on the sheet via filters but keep all the data in the background for calculations - there is a fairly simple technique for that. Post here if you want an example.

            • 3. Re: Sum underlying excel data for specific dimension values
              Paul Stephens

              Dimitri,

               

              Yes, I would like to filter data on the sheets and keep all data in background for calculations.  Specifically, I would like to have all data at my disposal and at the same time filter that data in various calculations.  I would love to see the example you have.

               

              Thanks,

              Paul

              • 4. Re: Sum underlying excel data for specific dimension values
                Paul Stephens

                Tracy,

                 

                Thanks for your response.  I've asked Dimitri to provide his suggested example as a possible solution.  Will keep you posted.

                 

                Thanks,

                Paul

                • 5. Re: Sum underlying excel data for specific dimension values
                  Dimitri.B

                  Paul,

                   

                  The idea is to use a table calculation like this one:

                   

                  LOOKUP(ATTR([YourFieldName]),0)

                   

                  on Filter shelf, instead of putting [YourFieldName] there.

                  See attached workbook for an example with Coffee Chain sample data.

                  • 6. Re: Sum underlying excel data for specific dimension values
                    Paul Stephens

                    Hi Dimitri/Tracy –

                     

                    I’m still struggling a bit with my goal here.  I could not figure out how to take Dimitri’s
                    suggestion and apply to my process.
                    Hopefully this example based on the Superstore sample (Tracy’s
                    suggestion) will make it easier to understand my goal.

                    • The profit tab simply shows total profit by
                      region, customer segment and quarter.
                    • The next tab has same layout but with profit
                      distribution for a given quarter/region across all segments within that region.
                    • I have a different excel with rebate numbers by
                      quarter and region.
                    • Using the ratios on the "Profit
                      distribution by segment" tab, I would like to take the rebate for a given
                      quarter/region and distribute across the 4 segments.  For example, the Central 2009 Q2 profit
                      distribution for the Consumer/Corporate/Home/Small Biz was 10%/18%/35%/37%.  I would like to take the Central 2009 Q2
                      rebate of $6,972 and distribute across those 4 segments and end up with rebate
                      values for Consumer/Corporate/Home/Small Biz of
                      $697.20/$1,254.96/$2,440.20/$2,579.64 (ignore rounding).
                    • I would then like to take this rebate metric and
                      add to profit for a final profit metric.
                      The two things that make this tricky are:
                      • The dynamically calculated metric must be made
                        at whatever level of detail the user has chosen on the report.  In this case it’s at the segment level.  However, if the user drills down to state
                        then I would like to see that calculation at the state level.  This means the ratio used to calculate rebate
                        at state level would be the profit at quarter/region/segment/state divided by
                        total rebate at quarter/region level.
                      • That fixed quarter/region rebate metric used in
                        the denominator of the ratio would have to remain constant used at appropriate points
                        of the report.  For example, in the “Profit
                        After Rebate” tab there are various dimension values filtered which should not
                        impact that constant denominator.

                     

                    I hope this long description of my ask is not confusing.

                    Thanks for your help.

                    • 7. Re: Sum underlying excel data for specific dimension values
                      Dimitri.B

                      Paul,

                       

                      see attached workbook for solutions. I used table calculations and data blending to calculate final profit as per your description, all stages are visible in the Profit distribution by segment tab.

                       

                      I am not sure what you mean by "if the user drills down to state". If the user is using Tableau Desktop, then simply replacing Customer Segment with State does the trick, no other adjustments needed (because of the way the table calculations are partitioned - compute using Pane (across)) - see Profit distribution by state tab. If you have some other drilling mechanism in mind, please provide details.

                       

                      The Profit after rebate tab illustrates the technique of filtering while preserving data for calculations, as described above. Changing filters only changes what is displayed/hidden, it does not affect the calculations.