Sales Incentive achieved calculation

I have an Annual Sales Goal for Bob of \$100.00. We will pay him a bonus each month based on his attainment of that goal. His total bonus he can achieve is \$10.

In Jan, he sold \$20. He has achieved 20% of his sales goal so I will pay him 20% of his total bonus. \$10(total bonus) * 20%(achieved) = \$2. My measure looks like this:

`Incentive Earned = [Total Bonus] * DIVIDE ( [Total Sales], [Sales Goal], 0 )`

How do I get it to stop calculating once the “DIVIDE ( [Total Sales], [Sales Goal], 0 )” goes over 100%?

Protect you calculation with some logic in an IF statement

``````   MyMeasure=
VAR WhoKnows = DIVIDE ( [Total Sales], [Sales Goal], 0 )
RETURN

IF( WhoKnows >1, "do something", "your calculation")``````
2 Likes

You could set up a ceiling measure like this one:

``````Pct Achieved =
IF (
DIVIDE ( [Total Sales], [Sales Goal], 0 ) >= 1,
1,
DIVIDE ( [Total Sales], [Sales Goal], 0 )
)
``````
• Brian
1 Like

Thank you both.

With a slight modification, @Melissa’s suggested DAX becomes useful in a wide variety of situations:

IF( WhoKnows >1, “do something”, " ¯\__(ツ)_/¯ ")

• Brian
2 Likes

It’s quite obvious I have no idea what to do, so it’s “do something” for me.
…and that do something is coming to this website and posting my “whoknows”.

3 Likes