Finding and adjusting only the first Max value in a range

Hi Community

I need to find the first max value in a range and adjust only that one.

In the attached file I have "Department code" in the first column, "application ID" in the second column and the "% share of costs" by Department code in the last column on the far right column. The % share of costs by Department code needs to total 100%, but each % by App ID can only be 5 decimals, so I usually have a rounding difference. I need to identify the Variance by Department code, identify the Max % share of costs value for each Department Code (done in columns 3 and 4), and then add the Variance to the Max value in the "% share of cost" column, which I can do with a simple formula.

My problem is, I don't know how to deal with a situation where there are multiple "Max" values for the same Department code. In the attached data file, for Department code 23746 the Max value of 0.25 appears twice. The same situation with Department code 89067 (Max of 0.12682 appears twice). As a result, my simple formula adds the variance of 0.00001 and 0.00013 respectively to both Max values, thereby causing further differences. In Excel I just use a Vlookup to find the first value, but I can't find a similar trick in Alteryx

I hope that makes sense. Any help or direction is appreciated.

Org2Serv with variances.xlsx

Tips and Tricks

Best Practices

Accepted answers

OllieClarke

Hey @BrianJ

A number of different ways of doing this, but here's where my mind went:

The trick is to sort descending by %, and then in the multi-row formula, we can set non-existent rows to NULL, group by Dept Code, and only add the variance if the previous row's dept code is null (i.e. it doesn't exist).

Hope that helps,

Ollie

var.yxzp

All comments

OllieClarke

Hey @BrianJ

A number of different ways of doing this, but here's where my mind went:

Hope that helps,

Ollie

var.yxzp

BrianJ

Hi @OllieClarke

Many thanks for your help. My Alteryx is a slightly older version so I'm unable to open your solution. However I followed your notes and couldn't make it work for my full list (I have 20 departments).

Here's what I did:

I added the Record ID and sorted by % share of cost descending. When I do that I get the max percentages mostly at the top . However, where I had two Max percentages in the same department, they are now one above the other.

I then do a Multi-Row formula, creating a New % column, Group by Dept Code and use the formula "IF ISNULL([Row-1:Dept Code]) THEN [% share of cost]+[Variance %]
ELSE [% share of cost]
ENDIF"

What am I missing?

Thanks again for your help.

BrianJ

I forgot to mention, I set the Value for Rows that Don't exist to NULL

BrianJ

Hi @OllieClarke

I checked the result using a Summarize function, but picked up the original "% share of cost" column instead of the New % column. As a result it looked like there were still differences. However, after working through everything I realized my stupid mistake, updated to the correct column and am very happy to confirm it worked perfectly (I'm not fully sure how yet 😁, but it does). I have no rounding differences.

Thank you so much for your help. It really is appreciated.

Brian

Quick Links

Unanswered
Community Events
Groups

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703