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

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.

• ###### 1. Re: Calculated Field to return the value of a Measure at a specified Pareto Chart intercept

Hey Andy,

Would you be able to attach a packaged workbook so our users can see what you've tried and give their own solutions a try?

Thanks,

-Diego

• ###### 2. Re: Calculated Field to return the value of a Measure at a specified Pareto Chart intercept

hi

could you tell what did this calculated field returning right now

• ###### 3. Re: Calculated Field to return the value of a Measure at a specified Pareto Chart intercept

Hi

You should use LOD and fixed it to year.

I dont have the workbook so its just guess.Please try and let me know if it works