count/sum values for distinct values in another field

I am new to Alteryx and I have a question that may be simple.

I have a dataset that contains Salesperson ID in column A, Count of Sales in Column B, and dollar amount of sale in column C.

Column B will always be 1 (one row per sale) and each salesperson will have multiple rows

I want to summarize the dataset so that each salesperson has one row and column B will be the count of sales for each unique salesperson. And column C will be the total dollar amount of all their sales.

I have looked into the summary tool but I don’t think that is the way to go because I don’t know how to limit the count to each unique (distinct) ID.

Thanks for your help.

Workflow

Accepted answers

Luke_C

Hi @ScottC_00

Can you provide some data? I don't see why the summarize tool wouldn't work with the following settings:

Group By Salesperson ID
SUM Count of sales (or count since it's 1 per record)
SUM Dollar Amount of Sales

All comments

Luke_C

Hi @ScottC_00

Can you provide some data? I don't see why the summarize tool wouldn't work with the following settings:

Group By Salesperson ID
SUM Count of sales (or count since it's 1 per record)
SUM Dollar Amount of Sales

ScottC_00

Hi Luke. Thanks for your reply and help.

The Summarize tool wasn't working because I didn't understand that I needed to group first.

Following your steps worked perfectly.

Beyond those steps I added the rest of the fields in the dataset and just used the "First" Action.

Does that make sense? Any advice on that?

Also I added a Select Tool after the Summarize tool so I could change my field names back to what they were (The action is prepended to each field name).

Is that the best practice for that issue?

Thanks again!

Luke_C

Hi @ScottC_00

Tough to say without seeing your data. First will just give you the first value for each group, so if that's what you want it should be fine.

For the field names, you can rename them directly in the summarize tool:

ScottC_00

Just discovered I don't need the Select tool. I can change the filed names right in the Summarize tool.

Thanks

ScottC_00

Perfect! Thanks Again Luke.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703