How to calculate per unit value based on specific selection criteria

utm0410
utm0410 Registered Posts: 2 ✭✭✭

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

Answers

  • HarizoR
    HarizoR Dataiker, Alpha Tester, Registered Posts: 138 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

Setup Info
    Tags
      Help me…