Forum Discussion

DIACHROMA's avatar
DIACHROMA
Icon for Helper II rankHelper II
5 years ago
Solved

Capped target in DAX

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:

NameGoalAchievedRemainingAchievement
A8086-6108%
B4036490%
C5052-2104%
D2018290%

 

Here is what I would like:

 

NameGoalAchievedRemainingAchievement
A80860100%
B4036490%
C50520100%
D2018290%

 

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

DAX
  • daxer-almighty's avatar
    daxer-almighty
    5 years ago

     

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

     

6 Replies

  • daxer-almighty's avatar
    daxer-almighty
    Icon for Solution Sage rankSolution Sage
    5 years ago

     

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

     

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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.