PowerPivot sales person point tier structure

1
votes

I'm trying to create a data model in which there are sales people who sell a variety of different product's. The problem comes in with the Tier structure for each product. Some products will receive different points according to sales about. some may have two to three tiers of points depending on sales amount. Other product may just be a flat payout. the then end the sales person gets his finally bounds as a percentage of his points depending on the Tier of number of points he receives for example

Product 1

  1. if volume 100 = 10 points
  2. if volume 200 = 20 points
  3. if volume 300+ = 30 points

employee payout

  1. 100 points = 20% of points payout
  2. 200 points = 50% of points payout
  3. 300 points = 150% if points payout.

I'm not sure how to structure this in the data model and calculate with DAX formula

Thanks for the help in advance

3
excelpowerpivotdax
are the amount of points per tier the same, but only the volume changes - or can both be different by product. I.e. would product 2 tiers be something like 150=10pt, 250=20pt, 350+=30pt (points being the same as prod 1, but volume different) - or rather 150=12pt, 250=22pt, 350+40pt (volume and points being different)? – Peter Albert

3 Answers

0
votes

Create new calculated column Lets Say, Now you will have Volume calculated column (IF ( Volume>=100 then 10 Volume >= 200 then 20) Person 1 Product 1 100 Person 2 Product 2 200

Person X Product X 300

Then add one more calculated column based on this calculated column to get percentage of volume.

Mark answer as correct if it helps.

0
votes

Try the following approach:

Data structure

Products:

enter image description here

Sales:

enter image description here

Data model

  • Load both tables into the Data Model (I called them Products and Sales)
  • In the diagram view, create a relationship between Sales[Product] and Product[Product]

DAX

This is the ugly part: In the sales table, as a new calculated column with the name Points. Use this DAX formula:

   =IF(Sales[Volume]<RELATED(Products[Volume Tier 1]),0,
    IF(Sales[Volume]<RELATED(Products[Volume Tier 2]),RELATED(Products[Points Tier 1]),
    IF(Sales[Volume]<RELATED(Products[Volume Tier 3]),RELATED(Products[Points Tier 2]),
    IF(Sales[Volume]<RELATED(Products[Volume Tier 4]),RELATED(Products[Points Tier 3]),
    IF(Sales[Volume]<RELATED(Products[Volume Tier 5]),RELATED(Products[Points Tier 4]),
    IF(Sales[Volume]>=RELATED(Products[Volume Tier 5]),RELATED(Products[Points Tier 5])))))))

Add a new measure with this formula: TotalPoints:=SUM(Sales[Points])

Now you can determine the number of points per transaction/sales person/etc. and use this in the subsequent steps.

Instead of using the really Volume Tiers, you could also leave non-relevant tiers blank in the Product table and extend your formula using the ISBLANK function.

-2
votes

I don't know about DAX but this will handle the Excel formulae.

Assuming volume in column A, to calculate points in column B:

$B2 = MIN(10*INT($A2/100),30)

Then I'm assuming you are going to aggregate points somewhere else (let's say in column D) and calculate payout in column E. My preferred way of doing this is to create a small lookup table somewhere. It looks like this:

Points     Payout Rate
   0        0
 100        0.2
 200        0.5
 300        1.5

Give the lookup table a name, e.g. PayoutRates. The formula to look up the payout rate, and calculate the payout is:

=$D2 * VLOOKUP($D2,PayoutRates,2,TRUE)

Alternatively, you can use nested IF statements to get the same result:

=$D2 * IF($D2<100,0,IF($D2<200,0.2,IF($D2<300,0.5,1.5)))