11 Replies Latest reply on Oct 6, 2014 8:54 AM by KK Molugu

    Percentage Meeting a Target (aggregation problem)

    Kelly Smith

      Hi Tableau Wizards,

       

      My teammates and I have been trying to solve this problem for weeks without success. We have a call center with representatives, supervisors, and managers. Each call a representative completes is either "resolved" or "not resolved", and from that we can calculate a percentage of calls resolved, for example Rep A resolved 90% of her calls, but Rep B only resolved 50% of his. The reps' target is 89% call resolution. The measure for Supervisors and Managers is the percentage of the reps under them that meet the target, for example, Supervisor X has 60% of her reps meeting the target and Sup Y has 80%.

       

      The data is at the individual call level, so then we aggregate it by rep to show the percentage of calls resolved. However, we can't figure out the next step of aggregating the reps' performance up to the supervisor and manager level based on the percent meeting target. Tableau doesn't have a COUNTIF function, which seems to be what we need, and the workarounds we've read about don't seem to work in this case. We run into Tableau's aggregation limits, whereby we can't count or sum an already aggregated measure, so we can't do the compound calculation (or whatever you might call it).

       

      You'll get a gold star if you help us figure this out!!

       

      Kelly from Oregon

        • 1. Re: Percentage Meeting a Target (aggregation problem)
          KK Molugu

          Hi Kelly,

           

          Welcome to to the Tableau forum. Would it be possible to attach the workbook you have or some sample data, so the forum members can easily answer your question.

           

          ..kk

          • 2. Re: Percentage Meeting a Target (aggregation problem)
            Kelly Smith

            I added an example file. Thanks for the suggestion.

            • 3. Re: Percentage Meeting a Target (aggregation problem)
              KK Molugu

              Great. Will take a look at this soon, as I am outside.

              ..kk

              • 4. Re: Percentage Meeting a Target (aggregation problem)
                KK Molugu

                Kelly:

                 

                Hope this is what you are looking for. I am more of a visual guy and I have created sheets with visual, but also created tabular ones to help out here. Explanation os sheets;

                1) Call Resolved By Rep

                - Showing calls resolved by rep and also showing the % of Yes and No by Rep

                 

                2) Call Resolved By Rep - NUM

                - Showing calls resolved by rep and also showing the % of Yes and No by Rep - Raw numbers

                 

                3) Rep Resolved To Target

                - As you have a target by Rep, I created a data set for each Rep with 89% target rate except for E as 50%

                - Only showing the Yes % records along with Target as the dotted line at the 89% mark. It shows that all the reps are below target. Shaded background tells you the 60% and 80% of the target. Used bullet chart here.

                - I also have a color calculation to show who met the target and who did not. Rep E is the only one who met the target and hence the green color line. Visually, easy to see who met or not met

                 

                4) Calls Resolved By Sup

                - Showing calls resolved by rep and also showing the % of Yes and No by Supervisor

                 

                5) Calls Resolved By Sup - RAW

                - Showing calls resolved by rep and also showing the % of Yes and No by Supervisor - Raw numbers

                 

                6) Sup To Target

                - Similar to Rep targets, I have created Sup target and did the same bullet charts to show if the Sup targets are met from the Rep's score

                - Here I did different targets for the supervisor

                - Again none of them met the target

                 

                7) Rep Target Raw - These are the raw target data

                8) Sup Target Raw - These are the raw target data

                 

                Hope this helps.

                 

                ..kk

                • 5. Re: Percentage Meeting a Target (aggregation problem)
                  Kelly Smith

                  Hi kk,

                  Thanks for you work on this! You've simplified the calculations from what I have in my actual workbook, and I really like the chart showing met/not met relative to the target. I'm going to use that. However, the final question still remains. I didn't do a very good job setting up the sample data. Let's say the target is 50% instead of 89% we have more reps meeting the target (I changed the source data to reflect this). The question my manager is asking is: what percentage of each supervisor's reps are meeting their target. So if Sup X has three reps, and two meet the target and one does not, then her "Percentage meeting target" is 67%. That's distinct from the average, where Rep A's call resolution rate is 33%, Rep B's is 50%, and Rep C's is 30%, and the rolled up average for Supervisor X is 36%. Am I making this more confusing?

                   

                  What I need is a table that shows the following (this reflect the updated worksheet):

                  SupervisorPercentage of Reps meeting Target
                  X33%

                  Y

                  83%
                  Z67%

                   

                  Basically I think the formula is, for each supervisor, COUNT( Reps Meeting Target )/ COUNT (Reps), but I can't get this to work because Tableau won't aggregate the aggregation of COUNT(Reps Meeting Target)-- "Reps Meeting Target" is already a calculated and aggregated field. I've tried doing this by creating a set of Reps Meeting Target, but again, Tableau won't count the members in the set because it won't aggregate again. I'm attaching your workbook with the target reset to 50%, just to make the example better to work with.

                   

                  I so appreciate your help!!!!

                   

                  Kelly

                  • 6. Re: Percentage Meeting a Target (aggregation problem)
                    KK Molugu

                    Kelly:

                     

                    I get exactly what you are asking and let me check into that tomorrow. Its an interesting problem...

                     

                    ..kk

                    • 7. Re: Percentage Meeting a Target (aggregation problem)
                      KK Molugu

                      Kelly:

                       

                      It is an interesting problem and I got to a point to show % by Sup (I am sure there might be other ways to solve this one too) and hopefully this is what you are looking for. It was an interesting night

                       

                      See attached workbook with sheets 'Intermediate Soln1' and 'Intermediate Soln2'. As we are aggregating the values at the rep level, I have to keep the Rep values on the detail shelf.

                       

                      I have used few calc fields to get the values and leveraged Index() operation to only show the top row by filtering.

                       

                      #1

                      Comm_Soln1_149674.PNG

                       

                      #2

                      Comm_Soln2_149674.PNG

                       

                      ..kk

                      1 of 1 people found this helpful
                      • 8. Re: Percentage Meeting a Target (aggregation problem)
                        Jim Wahl

                        I took a quick look at this before I realized KK had solved it

                         

                        But I have a slightly different solution, so I'll post a workbook and a few notes.

                         

                        You said that, "Tableau doesn't have a COUNTIF function, which seems to be what we need, ..."

                         

                        You can do this using conditionals in regular aggregations or table calc aggregations. For example, in your workbook you have a field

                        % Solved =

                          SUM([Number of Records]) / TOTAL(SUM([Number of Records]))

                         

                        TOTAL() makes this a table calc and means you can aggregate at a different level of detail.

                         

                        When you add Call Resolved to your view, the first SUM() is a regular aggregation and is therefore computed for each member of the Call Resolved dimension (here Yes and NO).  TOTAL(SUM()) --- when set to compute using Call Resolved --- is computed over all members of the Call Resolved dimension.

                         

                        But there's a simpler solution -- something more like a COUNTIF(). First start with a row level calc.

                         

                        Count Passed = SUM(IIF([Call Resolved]=="Yes", 1, 0))

                         

                        The inner part of this IIF("yes", then 1, else 0) is done at the row level. It's as if you're adding a new column to your data. Then the SUM() wrapped around this will aggregate at the level of detail in the view. So if you have Representative in the view, you'll sum the values over all rows for each representative. Using 1 and 0 for the if then else turns the sum in to a count.

                         

                        And now you can calculate

                        % Solved V2 = [Count Passed] / SUM([Number of Records])

                         

                         

                        This % Solved does not require a table calc and simplifies the next step, which is to aggregate the count of reps who passed for each supervisor.

                         

                        I did something similar to the above, but in this case you need to use a table calculation because you first need to aggregate at the rep level to calculate pass/fail and then you need aggregate the number of reps --- table calcs are required when there are two different levels of aggregation in a view.

                         

                        Count of Reps Who Passed =

                        IF FIRST() == 0 THEN

                            WINDOW_SUM(IIF([Pass V2] == "Green", 1, 0))

                        END

                         

                        When you add this to the view you want to set the compute using (aka addressing) to Rep, because you want to sum the number of reps who've passed for each supervisor (the other dimension in the view).

                         

                        The IF FIRST() == 0 THEN ... END is required because the table calc will be computed for each rep. This is fine if your view has rep in it as visible dimension. But if you're just showing the supervisors (and rep is on the Detail shelf), then you'll get multiple marks, which results in overlapping text or extra long bars.

                         

                        And, then finally to calculate the Percent of Reps Who Passed =

                            [Count of Reps Who Passed] / SIZE()

                         

                        Again, since compute using is set to Rep, the WINDOW_SUM() inside of Count of Reps... will be calculated across all reps for each supervisor, and SIZE(), which is the number of rows in a partition, will be calculated over Rep as well, which means you're getting the total number of reps for each supervisor.

                         

                        Note than in all of my calcs, the row-level Call Resolved field is not required (and in fact if you include this in the view, the % Solved also needs to be a table calc, as you originally had it). Using regular aggregates pushes a lot of the heavy lifting to the database, which will make for a faster, more memory efficient view.

                         

                        I've attached my version -- again, I took a quick look at KKs and I think you get to the same solution, but it's maybe helpful to see a different approach,

                         

                        Jim

                        1 of 1 people found this helpful
                        • 9. Re: Percentage Meeting a Target (aggregation problem)
                          KK Molugu

                          Jim:

                           

                          It always good to see different solutions. Nice one..

                           

                          ..kk

                          • 10. Re: Percentage Meeting a Target (aggregation problem)
                            Kelly Smith

                            You guys are awesome!!! I marked KK's solution as the correct answer because he got it first, but you both hit the nail on the head. You have saved me countless (additional) hours of frustration! If you lived in Portland, I'd buy you a drink!

                            • 11. Re: Percentage Meeting a Target (aggregation problem)
                              KK Molugu

                              No worries Kelly, community is here to help you out. Now, about that drink, will ping you when in Portland

                               

                              There are really nice tricks from Jim and I always learns from these masters as well.

                               

                              ..kk