Sign up to take part
Registered users can ask their own questions, contribute to discussions, and be part of the Community!
Registered users can ask their own questions, contribute to discussions, and be part of the Community!
Is there a way to sum column values and calculate a per unit value (Sales $ divided by Units Sold) based on a specific set of selection criteria? I have 28 characteristics related to each individual sales record and would like to be able to sum units and sales $ and calculate a $/Unit value based on any combination of these characteristics. Below is a snip of my data but I have included a sample file as well.
SAMPLE DATA | ||||||
Customer | Region | Sales Rep | Product | Units Sold | Sales $ | $/Unit |
Bristol | N | 1 | AAA | 7 | $10.50 | 1.50 |
Aaron | S | 2 | AAA | 9 | $51.75 | 5.75 |
Bella | E | 3 | CCC | 3 | $17.25 | 5.75 |
Bella | E | 1 | AAA | 10 | $15.00 | 1.50 |
Mikey | W | 2 | BBB | 12 | $39.00 | 3.25 |
Bella | E | 3 | BBB | 6 | $19.50 | 3.25 |
Bella | E | 1 | AAA | 2 | $3.00 | 1.50 |
Aaron | S | 2 | CCC | 5 | $28.75 | 5.75 |
Bristol | N | 3 | BBB | 8 | $26.00 | 3.25 |
Bristol | N | 1 | AAA | 20 | $30.00 | 1.50 |
Mikey | W | 2 | CCC | 10 | $57.50 | 5.75 |
Aaron | S | 3 | BBB | 2 | $6.50 | 3.25 |
Total | 94 | $304.75 | 3.24 | |||
Total Units, Sales $ and $/Unit by Product within Region | ||||||
Customer | Region | Sales Rep | Product | Units | Sales $ | $/Unit |
S | AAA | 9 | $51.75 | 5.75 | ||
S | BBB | 2 | $6.50 | 3.25 | ||
S | CCC | 5 | $28.75 | 5.75 | ||
16 | $87.00 | 5.44 |
Hi utm0410,
Since you are attempting to aggregate data, you will need to use a GroupBy recipe instead of a Prepare recipe.
In your case, the combination of characteristics will define the GroupBy keys, and the "Sum" aggregation will allow you to compute the total units/sales/etc.
To learn more about GroupBy recipes, you can watch this video and read the dedicated tutorial on the Knowledge Base.
Best,
Harizo