10 Replies Latest reply on Dec 9, 2018 11:36 AM by Yuriy Fal

    Aggregation in Tableau different than Power Pivot (Excel)

    Paulo Freitas

      Hi there,

       

      I have an issue that has been troubling me for quite a few days. I have imported an excel spreadsheet into Tableau containing 5 worksheets (each one represents normalized tables). Tableau properly recognizes them and is able to interpret the correct relationships between these tables:

      Cardinality: Relative Performance to Product Output (1 to many), and Product Output to Product (many to 1).

       

      I then create the table below (Facility Unit from Facility Table, Training Type from Training Table and R&D (the measure values below from 'Relative Performance' table):

      The problem is that the 2,639,270 in the first cell is not what I was expecting and it is 4x larger than it should be. Basically, it is caused by the cardinality from 'Relative Performance'-'Product Output'-Product, and the fact that there are 4 different products in the Product table, which causes the expected aggregated R&D value to be multiplied by 4 (the number of distinct products).

       

      I did the very same thing in Power Pivot and I do get the proper values:

       

      So, my question is: Does Tableau handle aggregation differently when you join multiple tables? If so, what should I do to get the proper expected value without the redundant aggregation? Is this a case where I should use ATTR or LOD calculations? If so, why does Tableau do that (I'm pretty sure that is a reasonable explanation)?

       

      Thanks,

      Paulo

        • 1. Re: Aggregation in Tableau different than Power Pivot (Excel)
          Paulo Freitas

          And here is the data model snapshot from Power Pivot, which is way more descriptive:

          • 2. Re: Aggregation in Tableau different than Power Pivot (Excel)
            Don Wise

            Hi Paulo,

             

            I'd say your joins in Tableau need to be revisited as to type of join? All of yours are currently INNER JOINS per your screenshot, whereas in PowerPivot, they are 1-to-many or many-to-1.  So the joins are likely the issue with the duplication.

             

            Some resources for you here: Join Your Data - Tableau   Thx, Don

            • 3. Re: Aggregation in Tableau different than Power Pivot (Excel)
              Paul Wachtler

              Hi Paulo,

               

              In the example you provided, since you're not using any fields from the Product Output or Product table, and that's causing the duplication, you could remove those from your data join.

               

              However, I imagine that you do want to use those tables eventually.  If you think about how you're joining though, your many to one relationship from product output to product is going to cause duplicated rows anytime there is more than one product for a product output - which is what's happening in this case.

               

              What you can do to get the results you desire, with the same join set-up, is to use an LOD calculation.  Something like this - replace measure value with the name of one measure.  You can create two calculations - one for each measure value, and place them on your measure value shelf.

               

              {fixed [facility unit], [training type], [date]: max([measure value])}

               

              If you place that on your measure value shelf, it'll probably default to aggregating as a sum which I believe will give you the numbers you're looking for.

               

              If you share your workbook though, It'd be easier to get this solution working.

               

              Best,

              Paul

              1 of 1 people found this helpful
              • 4. Re: Aggregation in Tableau different than Power Pivot (Excel)
                Paulo Freitas

                Thank you for your answer, Paul.

                 

                I did suspect the solution would be related to a LOD calculation, and I can confirm that your proposed LOD calculation did the trick (as per attached workbook). Btw, another simpler LOD calculated field I came up with was: {EXCLUDE [Product Id]: SUM([R&D Spending])}

                 

                However, an important question that comes to mind is why isn't Tableau smart as Power Pivot to understand that the product table should not affect the aggregation of a measure that is present only in the Relative Performance table? If was aggregating the Product Units field in the Product Output table, it would totally make sense. Having to create one LOD calculation field for each of the measures in the Relative Performance field is time consuming, inefficient and will clutter my list of measures - not to say it is also error-prone.

                 

                As well, If was to write the SQL query below:
                Select facility unit, training type, date, sum(R&D Spending)from Relative Performance

                group by facility unit, training type, date

                 

                would also yield the numbers I'm looking for, irrespective of the indirect relationship between Relative Performance and Product tables.

                 

                 

                Thanks,
                Paulo

                • 5. Re: Aggregation in Tableau different than Power Pivot (Excel)
                  Paulo Freitas

                  First of all, thanks for your response, Don.

                   

                  However, I don't think the type of join and tables cardinality are related. You can have, for example. a 1-to-many relationship that is either an inner, outer, left, right or full join. Btw, just to confirm I have made tests with all types of possible joins and the results, as I suspected, were all the same. No other type of join solved my problem.

                   

                   

                  Thanks,

                  Paulo

                  • 6. Re: Aggregation in Tableau different than Power Pivot (Excel)
                    Paulo Freitas

                    And now an even more bizarre situation. I have created the very same workbook twice (both attached) and the results are different. One has the over-counting values and the other does not. Now, I'm really confused. Could this be a bug in Tableau?

                     

                    If someone can shed a light, I would greatly appreciate.

                     

                     

                    Thanks,

                    Paulo

                    • 7. Re: Aggregation in Tableau different than Power Pivot (Excel)
                      Paul Wachtler

                      You're welcome Paulo.  Glad the LOD worked.  I'd appreciate if you can mark my answer as correct and helpful to close out this thread.

                       

                      To answer your question about Tableau vs. PowerPivot - the best description I've heard of Tableau is that it's a data driven drawing tool.  It can draw pretty much anything you tell it to but it's all based on your instruction.  It's not meant to be as customizable as SQL on the data prep side (unless you're actually using Tableau Prep - which can handle sophisticated data cleansing).  There's nothing in the default connection screen to tell Tableau that you want your aggregations set up differently than your joins are telling it.

                       

                      I'm not really familiar enough with PowerPivot to know how it handles aggregation, but it seems like there's some reason you're using Tableau instead of PowerPivot.  If that's the case, then LODs are your way to fix this.

                       

                      That, or if you're able to use SQL then by all means you should write a view like you described and import that as your datasource.

                       

                      Best,

                      Paul

                      • 8. Re: Aggregation in Tableau different than Power Pivot (Excel)
                        Yuriy Fal

                        Hi Paulo,

                         

                        The difference is with the datasource flag

                        Assume Referential Integrity.

                        Setting this flag to ON enforces Join Culling.

                         

                        Yours,

                        Yuri

                         

                        BTW, there was a quick demo at the TC18 Opening Keynote 

                        showing the upcoming Tableau with the Data Model Capabilities

                        (starting from the 01:06:35 of the keynote video below):

                         

                        Tableau Conference 2018 Opening Keynote - YouTube

                         

                        Right now i'm testing a pre-release (alpha) version of TD with DMC.

                        1 of 1 people found this helpful
                        • 9. Re: Aggregation in Tableau different than Power Pivot (Excel)
                          Paulo Freitas

                          Thanks, Yuri! It worked!! That was basically the difference in the output between my two versions. I actually don't even need the calculated fields anymore.

                           

                          I wish I could mark two answers as the correct one.

                           

                          Paulo