Forum Discussion
Capping variance at 999%
Please try:
spl MTD VAR% Gross Activation = VAR __BASELINE_VALUE = [spl MTD Gross Activation] VAR __VALUE_TO_COMPARE = [MTD Gross Activation] VAR __VALUE = IF ( NOT ISBLANK ( __VALUE_TO_COMPARE ), IF ( ISBLANK ( DIVIDE ( __VALUE_TO_COMPARE - __BASELINE_VALUE, __BASELINE_VALUE ) ), VALUE ( "0.00" ), DIVIDE ( __VALUE_TO_COMPARE - __BASELINE_VALUE, __BASELINE_VALUE ) ) ) VAR __RESULT = MIN ( __VALUE, 9.99 ) RETURN __RESULTBest Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
spl MTD VAR% Gross Activation = VAR __BASELINE_VALUE = [spl MTD Gross Activation] VAR __VALUE_TO_COMPARE = [MTD Gross Activation] VAR __VALUE = IF ( NOT ISBLANK ( __VALUE_TO_COMPARE ), IF ( ISBLANK ( DIVIDE ( __VALUE_TO_COMPARE - __BASELINE_VALUE, __BASELINE_VALUE ) ), VALUE ( "0.00" ), DIVIDE ( __VALUE_TO_COMPARE - __BASELINE_VALUE, __BASELINE_VALUE ) ) ) VAR __RESULT = SWITCH ( TRUE (), __VALUE < -9.99, -9.99, __VALUE > 9.99, 9.99, __VALUE ) RETURN __RESULTBest Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
Please try:
spl MTD VAR% Gross Activation =
VAR __BASELINE_VALUE = [spl MTD Gross Activation]
VAR __VALUE_TO_COMPARE = [MTD Gross Activation]
VAR __VALUE =
IF (
NOT ISBLANK ( __VALUE_TO_COMPARE ),
IF (
ISBLANK ( DIVIDE ( __VALUE_TO_COMPARE - __BASELINE_VALUE, __BASELINE_VALUE ) ),
VALUE ( "0.00" ),
DIVIDE ( __VALUE_TO_COMPARE - __BASELINE_VALUE, __BASELINE_VALUE )
)
)
VAR __RESULT =
MIN ( __VALUE, 9.99 )
RETURN
__RESULT
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
Thank you so much! One more thing, I forgot to add, the bottom cap sorry. So it must not exceed 999% and not be lower than -999%, how can we add -999% to the dax you just did?
spl MTD VAR% Gross Activation = VAR __BASELINE_VALUE = [spl MTD Gross Activation] VAR __VALUE_TO_COMPARE = [MTD Gross Activation] VAR __VALUE = IF ( NOT ISBLANK ( __VALUE_TO_COMPARE ), IF ( ISBLANK ( DIVIDE ( __VALUE_TO_COMPARE - __BASELINE_VALUE, __BASELINE_VALUE ) ), VALUE ( "0.00" ), DIVIDE ( __VALUE_TO_COMPARE - __BASELINE_VALUE, __BASELINE_VALUE ) ) ) VAR __RESULT = SWITCH ( TRUE (), __VALUE < -9.99, -9.99, __VALUE > 9.99, 9.99, __VALUE ) RETURN __RESULTBest Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
Anonymous