3 Replies Latest reply on Oct 2, 2018 1:06 PM by Jim Dehner

# How to find the percentile of an aggregate in tableau and plot the values for a lift curve?

I have three issues in tableau that I am trying to solve in order to complete an assignment for my company. I cannot attach the actual data but i am attaching a dummy worksheet on similar lines.

Problem: 1 sheet 1. I have to plot % cumulative bads versus %cumulative goods. Here i define bads as having profit less than average profit. the example is in sheet 1 of the workbook. First i assign goods or bads based on the profit levels for each order id and then i do a running sum to get the cumulative bads and goods and try to plot them but i get a single point instad of a line plot because the functions are aggregate. Now i know that i cannot use fixed because my calculations of cumulatives will change when i use filters on actual data.

Problem: 2 sheet 2. Plotting cumulative population versus cumulative bads. The question is quite same as the previous one.

Problem: 3 sheet 3. In this one i have to calculate the lift for the cumulative bads curve i.e the value of the cumulative bads based on 5 percentile,10,20,30 50 so on and so forth. for this i have created a calculted field percentile which should categorise the %cum bads based on the filters. I can calculate the percentile cum bads for a population but not for a aggregate such as%cum bads.

Any help provided by any of you is welcome as i have explored enough options till now but not able to find a solution.

I am attaching a dummy worksheet as i cannot upload actual data. If anyone is willing enough please share your email we can have a short chat on hangouts.

• ###### 1. Re: How to find the percentile of an aggregate in tableau and plot the values for a lift curve?

I looked at your book and have questions

with respect to the first and second question you you are using a running total - but the expression needs a dimension to "run across'

there is nothing in the viz and therefore nothing in the table to run across

JIm

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: How to find the percentile of an aggregate in tableau and plot the values for a lift curve?

Say I want to run across all order IDs and then plot %cum bads vs

%cumngoods without plotting order I'd in any sense

On Tue, Oct 2, 2018, 10:54 PM Jim Dehner <tableaucommunity@tableau.com>

• ###### 3. Re: How to find the percentile of an aggregate in tableau and plot the values for a lift curve?

I'm not tracking with you - in a business sense don't know what you are trying to show

I'm also not tracking with the analysis

the plot that you made without "Order id" in the vies is only a single data point (i.e. the underlying table is a single value

with that there is nothing to use in a running sum

if I drop order ID on the detail there are now a total of 5009 marks - which is the distinct count of all the orders in the d/b

the formula you are using to determine Bad or Good orders is based on percentile =.5

percentile works on the number of marks in the table - at .5 it returns the value at which 50% of the marks are above the value and 50% below

that said when you plot

this will always form a line of approximately 45% and will end at 100%

so going full circle - not certain what the business question is here and how to create a viz that will help with the understanding

sorry

Jim