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

**Andy Murch**Feb 7, 2017 3:45 AM

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.