3 Replies Latest reply on Feb 13, 2017 8:54 PM by Nipun Garg

    Calculated Field to return the value of a Measure at a specified Pareto Chart intercept

    Andy Murch

      Tableau Version:  10.0.6

       

      Hello All,

       

      I'm hoping someone can point me in the right direction with returning a value using a calculated field.

       

      ---The Plan---

      Perform a Heavy, Medium, Light analysis on order size year-on-year; where Heavy = Top 20% of orders by number of units, Medium = next 30% of orders by number of units, Light = the remaining 50% of orders.

       

      i.e. list all orders and the total units for each order, sorted by most units to fewest units.  Create a % of running total of units for those orders, then determine the smallest number of units in an order which falls into the top 20% (the cut-off for "Heavy").  Then do the same for the next 30% of orders, to determine the cut-off point for "Medium".  The rest will be "Light".

       

      The end goal would be to see a chart showing, for each of the "Heavy", "Medium" and "Light" sets, what the cut-off points (number of units) were year-on-year - to analyse order sizes.

       

      ---The Situation---

      I have created a Pareto Chart (for a single year, which can be amended using a filter), showing % of Running Total of Units (y-axis) against % of Running Total of Countd of Order ID (x-axis).

       

      I have then created the following calculated fields, in order to display a vertical reference line which intercepts at 20% of the running total of Units and at 50% of the running total of Units:

       

      % of Orders = RUNNING_SUM(COUNTD([Order ID])) / TOTAL(COUNTD([Order ID]))

      % of Units = RUNNING_SUM(SUM([Units]))/TOTAL(SUM([Units]))

      20% Vertical Reference = IF [% of Units] <= 0.2 THEN [% of Orders] ELSE NULL END

      50% Vertical Reference = IF [% of Units] <= 0.5 THEN [% of Orders] ELSE NULL END

      I then dragged in a reference line, and set the Value to the Maximum of 20% Vertical Reference (and repeated for the 50% Vertical Reference).

       

      This works fine, and I can see on my chart where the cut-off is for the Top 20% and next 30%; however, I am currently unable to return the value for the number of units at these intercepts - only the value for the % running total of countd of orders!

       

      ---The Question---

       

      Is it possible to return, for each year, what was the smallest number of units in an order, from all those orders which fell into the Top 20% of orders (based on total units in each order) for each year?

       

      If I list all the information in a table, it's easy to see what the answer is; however, I'm unable to created a calculated field (which Tableau accepts), to return the correct answer.  The following did not work:

       

      IF [% of Units] <= 0.2 THEN MIN([Units]) ELSE NULL END

       

      But I don't know why!?

       

      Any help would be greatly appreciated!

       

      Many thanks.