Target vs volume

Hi,

How can I calculate incentive on the basis of volume achieved and targets?
My targets and volume are in different tables, I’ve derived a % increase or decrease from it.

Now I want to calculate Incentive Payout on the basis of achievement, I’ve another data set which only includes achievement and payout %(as shown below).

Attainment Payout
79% 0%
80% 70%
90% 85%
100% 100%
110% 115%
125% 135%
150% 160%
200% 220%
250% 300%
300% 380%
400% 495%
500% 1000%

Now I want to multiply the attainment% by payout%. The payout amount is fixed at 90,000.
So ideally if attainment is 80% then 90,000 * 70/100 and so on.

I’ve attached my pbix file as well.

Incentive Plan.pbix (1.4 MB)

Hi,

Any ideas on this?

First thing I would do is merge all of your TARGETS tables into one like the file I have attached. This will make it much easier for you to setup measures. You will also see where I added a Target Date column to help distinguish what month it is. Incorporate this into your model and have a go at it. If you get stuck, let us know here on the forum and we will assist.

** You are more than likely going to use a SWITCH statement to accomplish your solution above**

Here is a video from Sam, in case you are not familiar with the logic

Incentive Targets- EDNA Forum.csv (18.7 KB)

Thanks
Enterprise%20DNA%20Expert%20-%20Small

1 Like

Hi,
You beat me to it :grinning:
Yes, I agree. Many ways to achieve this.
Another solution would be to enter this table in Enter Data, adding a min bin column , with Attainment being the max column and Payout the value column. The classification this way also allows to create a slicer to filter on the individual bins.

Paul

Hi @JarrettM / @Paul

Thanks for your insights I did combined that table and assigned dates to the targets as well.
But I’ve assigned the month end date, so do you think this would be causing issue in my calculation as I’m not getting correct targets for all the SM.

I’ve used the following measure :

Targets = CALCULATE(SUM( 'Jan-Mar Targets'[Target]) , FILTER( 'Jan-Mar Targets', 'Jan-Mar Targets'[Month] > EOMONTH(TODAY(), -1 ) && 'Jan-Mar Targets'[Month] <= EOMONTH( TODAY() , 0 ) ) )

Please give more insights regarding the same.

Thanks
Ankit

Please keep the Dates the way I had them. You need the 1st of the month, not end of the month if I’m understanding your last post. Once you have this complete, post an updated version of you PBIX.

Thanks
Enterprise%20DNA%20Expert%20-%20Small

Hi @JarrettM,

I’ve tried doing it 1st of the month as well. But still facing the same issue.
For Manav, Target should be 1 for Jan. But I’m getting 3 only which is the target for Feb.

image

I’m attaching my pbix as well. Please let me where I’m going wrong.

Incentive Plan.pbix (2.1 MB)

Thanks
Ankit

Hi @kkrj.ankit,

Please check below formula. Just quickly looking into your file, It is giving correct result of Manav Garg.

Targets =
CALCULATE (
    SUM ( 'Jan-Mar Targets'[Target] ),
    FILTER (
        'Jan-Mar Targets',
        'Jan-Mar Targets'[Month] >= MIN ( 'Calendar'[Date] )
            && 'Jan-Mar Targets'[Month] <= MAX ( 'Calendar'[Date] )
    )
)
1 Like

Hi @hafizsultan

Cheers :beer:

Thanks, I don’t know why I didn’t use it with dates, instead of EOMONTH function.

Thanks for your effort.

-Ankit

HI @kkrj.ankit,

Thanks for your prompt checking of results. Yes, with combination of EOMONTH and TODAY function, it would have given you static value for particular month in that case.

Agree. Thanks once again.

One other thing I would do is setup a proper Calendar Table. I noticed that the Months and Month and Year Columns did not sort correctly. Sam has a great way to setup a proper Calendar Table. Here is a video and document for you to copy and paste into the Query Editor.

** The second document that includes offsets is something you can use as well that I picked up from @Melissa here in the forum**

Power BI Date Table code.txt (2.8 KB)
Power BI Date Table Code with Offsets.txt (4.7 KB)

Thanks
Enterprise%20DNA%20Expert%20-%20Small

2 Likes

Sure, I’ll work on the cosmetic changes now as I’m done with my data sanity.

Thanks @JarrettM

1 Like