Do you know the History of Data Science? READ MORE

How to calculate per unit value based on specific selection criteria

utm0410
Level 1
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     
CustomerRegionSales RepProductUnits SoldSales $$/Unit
BristolN1AAA7$10.501.50
AaronS2AAA9$51.755.75
BellaE3CCC3$17.255.75
BellaE1AAA10$15.001.50
MikeyW2BBB12$39.003.25
BellaE3BBB6$19.503.25
BellaE1AAA2$3.001.50
AaronS2CCC5$28.755.75
BristolN3BBB8$26.003.25
BristolN1AAA20$30.001.50
MikeyW2CCC10$57.505.75
AaronS3BBB2$6.503.25
Total   94$304.753.24
       
 Total Units, Sales $ and $/Unit by Product within Region  
CustomerRegionSales RepProductUnitsSales $$/Unit
 S AAA9$51.755.75
 S BBB2$6.503.25
 S CCC5$28.755.75
    16$87.005.44
0 Kudos
1 Reply
HarizoR
Dataiker
Dataiker

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

0 Kudos
A banner prompting to get Dataiku DSS