Discover this year's submissions to the Dataiku Frontrunner Awards and give kudos to your favorite use cases and success stories!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
Developer Advocate
Developer Advocate

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