topic Re: Capped target in DAX in DAX Commands and Tips

Capped target in DAX

DIACHROMA — Fri, 23 Jul 2021 13:50:27 GMT

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

Re: Capped target in DAX

Greg_Deckler — Fri, 23 Jul 2021 13:59:25 GMT

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.

Re: Capped target in DAX

Anonymous — Fri, 23 Jul 2021 14:00:48 GMT

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] ;

Achieved - Goal )

Re: Capped target in DAX

Anonymous — Fri, 23 Jul 2021 14:41:22 GMT

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.

Re: Capped target in DAX

daxer-almighty — Sat, 24 Jul 2021 09:10:44 GMT

// 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] )

Re: Capped target in DAX

DIACHROMA — Wed, 28 Jul 2021 08:13:57 GMT

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

THAAAANKS !!!!

Pauline

Re: Capped target in DAX

tausif124 — Thu, 06 Apr 2023 01:42:34 GMT

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