How to calculate per unit value based on specific selection criteria
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 |
Answers
-
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