Capped target in DAX

[DIACHROMA]

Hello Dax Lovers, 

I need some help 😊

I would like to calculate a rate of progress in relation to an objective, but this rate must be capped at 100%.

Example of what I currently have:

Name	Goal	Achieved	Remaining	Achievement
A	80	86	-6	108%
B	40	36	4	90%
C	50	52	-2	104%
D	20	18	2	90%

Here is what I would like:

Name	Goal	Achieved	Remaining	Achievement
A	80	86	0	100%
B	40	36	4	90%
C	50	52	0	100%
D	20	18	2	90%

I tried the below formula : 

% Achievement =

VAR Numerator = IF( [Achieved] > [Goal]  ;

[Goal]  ;

[Achieved] )

VAR Denominator = [Goal]

RETURN

DIVIDE( Numerator ; Denominator ; 0 )

But when I put the measure in a table or matrix, it loads endlessly... (I also tried without variables). 

Do you have any idea of what's wrong ? All of my other measures are really fast so I don't think it is a model issue. 

In addition, to calculate the "Remaining" I'd like to have 0 if the achieved is greater than the goal but using "if is greater than ... then ..." either I lose my total, or the measure is very long when loading.

Many thanks in advance for your help !

Pauline

[daxer-almighty · Accepted solution]

// If such measures do not work blazingly fast,
// then you have a problem in your model. 100%.

[Capped Achievement] = MIN( 1, DIVIDE( [Achieved], [Goal] ) )

[Surplus] = MAX( 0, [Achieved] - [Goal] )

[tausif124]

@daxer-almighty can you explain to me what the cap achievement measure is doing? I just tried it for my need and its working.

[DIACHROMA]

Hi @daxer-almighty , it works perfectly and it's super fast!

THAAAANKS !!!!

Pauline

[Anonymous]

Can you try:

Assuming Acheived and Goal are measures. If not then create them as Measures with SUM function.

% Achievement =

IF( [Achieved] > [Goal]  ;

100  ;

DIVIDE( Achieved; Goal; 0 ))

Remaining =

IF( [Achieved] > [Goal]  ;

0;

Achieved - Goal )

[Anonymous]

A correction:

Add a new calculated column for Acheivement

NewAcheived = IF(table[Acheived] > table[Goal], table[Goal], table[Ach])

then use this calculated column instead of original column as below:

Remaining = sum(table[Goal]) - SUM(table[NewAch])

Acheive Rate = DIVIDE(SUM(table[NewAch]), sum(table[Goal]))

No need for IF else.

[Greg_Deckler]

@DIACHROMA Very strange in terms of you having a performance issue with that measure. However, perhaps simply take the approach of taking what you currently have and adding an IF statement. IF %achievement > 1, 1, %achievement. Assuming you have that in a VAR.