Filter for first N of rows until row value exceeds X % with different groupings

[beelix]

Hoping someone could help me with this.

I have a table containing Profit for 3 different businesses (first 3 columnns). For each Business, I want to identify the key countries contributing to Business Profit.  As Profit can be a gain or loss, Countries contributing large losses should also be included.

So far, I created 4th to 6th columns:

• 4th Column - Sub Total of Profit for each Business 
• 5th Column  - Country Profit divide by Total Profit Per Business -e.g. $250K / $415K for first row.
• 6th Column - Cumulative % of 5th column (grouping based on each Business)

The criteria to identify major driver is to include the rows up to the point cumulative % is greater than 90% but it also has to be less than 130% (e.g. for Business #4, if I simply use > 90%, i would only identify France but omit Belgium and UK which is not the objective).  Once I meet the criteria, I want to exclude all rows which are deemed to be minor contributors.

In the 7th column, I have manually identified the rows which I want Alteryx to be able to filter for. If someone could help, that would be appreciated.

Thanks

Beelix

Business	Country	Profit	Total Profit Per Business	Country Profit to Total Profit Per Business (%)	Cumulative Profit to sub-Total (Per Business)
Business #1	United Kingdom	250,000	415,000	60%	60%	Include
Business #1	Australia	100,000	415,000	24%	84%	Include
Business #1	France	60,000	415,000	14%	99%	Include
Business #1	China	30,000	415,000	7%	106%
Business #1	Belgium	-25,000	415,000	-6%	100%
Business #2	United Kingdom	175,000	215,500	81%	81%	Include
Business #2	Australia	-70,000	215,500	-32%	49%	Include
Business #2	United States	63750	215,500	30%	78%	Include
Business #2	China	25,500	215,500	12%	90%	Include
Business #2	Belgium	21,250	215,500	10%	100%
Business #3	Australia	-60,000	22,000	-273%	-273%	Include
Business #3	France	58,000	22,000	264%	-9%	Include
Business #3	Belgium	36,000	22,000	164%	155%	Include
Business #3	United Kingdom	-14,000	22,000	-64%	91%	Include
Business #3	China	2,000	22,000	9%	100%
Business #4	France	100,000	26,000	385%	385%	Include
Business #4	Belgium	-95,000	26,000	-365%	19%	Include
Business #4	United Kingdom	20,000	26,000	77%	96%	Include
Business #4	China	1,000	26,000	4%	100%

[aatalai]

@beelix  I started by giving a record ID grouped by businesses, and converted the percentage figure into double format, then found out the first time the cumulative percentage went over 90% (by filtering for values greater than 90% and then grouping by business and taking the minim record ID) then joining that as the threshold and filtering that the original record IDs are lower than the threshold just calculated or the percentage is over 130, hope this helps, please let me know how you get on 

[PangHC]

@beelix maybe using Total movement (where SUM ABS) instead of net profit?
it ease to do the analysis.

Where max is 100%, it ease to find the main player. or filter by it own percentage.

for example, 

[Pilsner · Accepted solution]

Hello @beelix 

I've used a multi row formula to try and tackle this problem. Here are my steps:

1) Create a column which is the numeric value from within the original cumulative profit... column.

2) Use a multi row to flag any values that are between 90 - 130. (Don't forget to group by each business)

3) Use a formula, to create a column, that says "include", based on the value created by the multi row formula. (we want to keep all rows with value 0 or 1).


Here's the full workflow.

 

I've attached the workflow with annotations below, please let me know if you have any questions

Regards - Pilsner

[beelix]

Thanks Pilsner.  Worked very well!

[Pilsner]

That's great to hear, thank you for the feedback!