7 Replies Latest reply on Aug 17, 2016 11:43 AM by Scott Bullock

    Aggregate measure with Table calculation disappears when filtering the Dimension

    Scott Bullock

      I've been wrestling with this for the past month, working it every which way I can think of. The two primary elements in this problem are one Dimension (Project Sponsor) and one Measure (Delivery Performance).  The set up I have so far is this:

       

      Dimension: Project Sponsor - static field from input data with 4 values possible

      Measure: Delivery Performance - calculated cell in source data (Excel) that yields a percent rating.

      The calculated fields build off of the predecessor.

       

       

      1st Calculated field - Month over Month (MoM)

      (ZN(AVG([Delivery Performance])) - LOOKUP(ZN(AVG([Delivery Performance])), -1)

      This yields the expected result in the Tableau worksheet

       

      2nd Calculated field - KPO Mark Sustain

      IF ([MoM]) > .50

      THEN "4"

      ELSEIF ([MoM]) >= .30 and ([MoM]) <= .5

      THEN "3"

      ELSEIF ([MoM]) >= .15 and ([MoM]) < .30

      THEN "2"

      ELSEIF ([MoM]) >= -.15 and ([MoM]) < .15

      THEN "1"

      ELSEIF ([MoM]) >= -.30 and ([MoM]) < -.15

      THEN "2"

      ELSEIF ([MoM]) >= -.50 and ([MoM]) < -.30

      THEN "3"

      ELSEIF ([MoM]) < -.50

      THEN "4"

      END

       

      3rd Calculated Field - KPO AVG

      Window_Avg(FLOAT([KPO Mark Sustain]))

       

      The problem I am encountering is that the aggregated measure gets muddied after the first calculated field.  When filtering on the Dimension, it is defaulting to the next entity on the list, not apportioning the remaining 3 dimension values correctly.

      If I add the dimension to the Columns shelf, each dimension has the exact same value - it doesn't separate based on the vertical. This doesn't happen on the 1st calculated field - only where I'm trying to show granularity on the 3rd.

       

      Has anyone else experienced this?  I've researched multiple tutorials but can't quite get it.

       

        • 1. Re: Aggregate measure with Table calculation disappears when filtering the Dimension
          swaroop.gantela

          Scott,

           

          It feels to me like the issue is one of adjusting the "Compute using" and  "Restarting every"

          for each of your calculations.

           

          I mocked up some data and mocked up a workbook. Not sure if they correctly match

          your setup, but please adjust them and repost, if needed.

           

          Below is how the Table Calculation for MoM was set up:

          212544filt.png

          1 of 1 people found this helpful
          • 2. Re: Aggregate measure with Table calculation disappears when filtering the Dimension
            Scott Bullock

            MOM.png

            Thanks for the detail! I adjusted my worksheet to mirror what you had with the table calculation on (MoM) but as you can see the MoM and KPO Mark Sustain are null for the first group in Sponsor (dimension).  It is defaulting to mirror Dimension value B again as before. This hasn't shown up in the 20+ other various worksheets I've done with the same data set.

             

            One thought (admittedly a grasping-at-straws one):    Could the ZN LOOKUP on (MoM) be negating the first entry as there is the -1 command?  That is to say, since I have a defined time range (although the data extends farther back) could it be voiding out Dimension Value A since it doesn't have a value before it? Nothing comes before value A.

             

            The ZN LOOKUP calculation is targeted to Delivery Performance, but I'm wondering if it is linking it to Sponsor somehow?

            • 3. Re: Aggregate measure with Table calculation disappears when filtering the Dimension
              swaroop.gantela

              Scott,

               

              Yes, the nulls are on account of the first entry having nothing to lookup.

              It looks like the current Addressing is setting the whole first Sponsor to Null,

              but really only the first month should be null.

               

              I think the next step will be to make sure that all four of the table calculation pills

              (MoM, KPO Mark Sustain, and KPO Avg Sustain in both places) have the

              identical Addressing and Partitioning. Then if you would, please post screenshots

              of both the Table Calculation window and the Advanced window (as shown in

              my previous message) for one of them, say KPO Avg Sustain.

               

              Another question, should the KPO Avg Sustain be the same for all lines

              of the same Sponsor?

              • 4. Re: Aggregate measure with Table calculation disappears when filtering the Dimension
                Scott Bullock

                Hi Swaroop,

                 

                The KPO AVG Sustain would be the same in each sponsor group as an aggregate of the KPO Mark Sustain values over the date range. So it would be a running average - I don't require a distinct calculation for every month.

                 

                For the table calculation portion on each pill - I did update each and made sure the Addressing was ordered correctly. There was an interesting outcome.

                 

                Screenshot 1 shows the 3 pills updated that are in the Rows Shelf. It finally populates MoM and KPO Mark Sustain!  Of course it's showing null for the first month, but progress!  KPO AVG Sustain still doesn't populate, and I noticed the KPO AVG Sustain column on the last right is identical in each Sponsor group.

                 

                Screenshot 2 shows the 3 pills updated that are in the Rows Shelf and the one in the Label section as well. This one shows a corrected KPO AVG Sustain in the far right column.

                 

                MOM_3.4.1.pngMOM_3.4.2.png

                 

                I really appreciate your perspective and help on this!

                • 5. Re: Aggregate measure with Table calculation disappears when filtering the Dimension
                  swaroop.gantela

                  Scott,

                   

                  Hmm. Your addressing looks exactly like what I have for my KPO Avg.

                  Will need to think some more as to why the 1st Sponsor has all nulls.

                   

                  With regards to the first month's MoM and KPO Mark being null, I think

                  that is always going to be the case on account of that being the first data point

                  in the view. If you do want it to populate based on a value not in the view

                  (i.e. December of the previous year), that will take a bit more work.

                   

                  I did notice a discrepancy: in the view, you are displaying the SUM(Delivery Performance),

                  but in MoM, you are using AVG(Delivery Performance), and so the numbers

                  on the visualization give the appearance of being inconsistent.

                  • 6. Re: Aggregate measure with Table calculation disappears when filtering the Dimension
                    swaroop.gantela

                    Scott,

                     

                    It may have been overkill with the Addressing.

                    You may want to try different "Compute usings"

                    for [KPO Avg]. I tried using simply just [Date],

                    and it seemed like the averages stayed the same.

                    • 7. Re: Aggregate measure with Table calculation disappears when filtering the Dimension
                      Scott Bullock

                      That seems to have resolved it for me!  Had to do a few additional tweaks, but it finally allowed the segmentation I needed.  Thanks!