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 BusinessCountryProfitTotal Profit Per BusinessCountry Profit to Total Profit Per Business (%)Cumulative Profit to sub-Total (Per Business) Business #1United Kingdom250,000415,00060%60%IncludeBusiness #1Australia100,000415,00024%84%IncludeBusiness #1France60,000415,00014%99%IncludeBusiness #1China30,000415,0007%106% Business #1Belgium-25,000415,000-6%100% Business #2United Kingdom175,000215,50081%81%IncludeBusiness #2Australia-70,000215,500-32%49%IncludeBusiness #2United States63750215,50030%78%IncludeBusiness #2China25,500215,50012%90%IncludeBusiness #2Belgium21,250215,50010%100% Business #3Australia-60,00022,000-273%-273%IncludeBusiness #3France58,00022,000264%-9%IncludeBusiness #3Belgium36,00022,000164%155%IncludeBusiness #3United Kingdom-14,00022,000-64%91%IncludeBusiness #3China2,00022,0009%100% Business #4France100,00026,000385%385%IncludeBusiness #4Belgium-95,00026,000-365%19%IncludeBusiness #4United Kingdom20,00026,00077%96%IncludeBusiness #4China1,00026,0004%100%

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

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%

Preparation

Accepted answers

Pilsner

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.

Screenshot 2025-07-18 113159.png

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

Regards - Pilsner

New Workflow4.yxmd

All comments

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

Screenshot 2025-07-18 102949.png

Filter for first N of rows until row value exceeds X.yxmd

AncientPandaman

@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,
Screenshot 2025-07-18 182954.png

Pilsner

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.

Screenshot 2025-07-18 113159.png

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

Regards - Pilsner

New Workflow4.yxmd

beelix

Thanks Pilsner. Worked very well!

Pilsner

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15879

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703