Forum Discussion

aabdal03's avatar
aabdal03
New Member
1 year ago
Solved

dax with chARTICULATOR VISULA

for actual2 = 1.87 , target2 = 0 , the goal direction is = 1 , calculation F = deviation the result from charticulator is 142% while it should be 98.13%
need to know what is the problem why charticulator does not display the right result ?

KPI Achievement Per Month =
VAR PreAggregateResults =
SUMMARIZE(
VALUES('01.00sc supplychain'[KPI]),
'01.00sc supplychain'[KPI],
"SingleKPIResult",
VAR GoalDir = MAX('01.00sc supplychain'[Goal direction])
VAR IsCalculateSelected =
CONTAINS(
ALLSELECTED('02.sc slicer'),
'02.sc slicer'[Slicersc],
"07.calculate"
)
VAR ActualMeasure = IF(IsCalculateSelected, [calc actual2], [actual 2])
VAR TargetMeasure = IF(IsCalculateSelected, [Calc Target 2], [target 2])
VAR CalcType = FIRSTNONBLANK('01.00sc supplychain'[calculation f], 1)
VAR IsActualZero = ActualMeasure = 0
VAR IsTargetZero = TargetMeasure = 0

// Edge case handling for zero values
VAR EdgeCaseResult =
IF(
AND(IsActualZero, IsTargetZero),
1,
BLANK()
)
// Main calculation logic
VAR ResultValue =
IF(
NOT ISBLANK(EdgeCaseResult),
EdgeCaseResult,
SWITCH(
CalcType,
"deviation",
IF(
IsCalculateSelected,
IF(
TargetMeasure <> 0,
((1 - ((ActualMeasure - TargetMeasure) / TargetMeasure)) / (1 - [target 2])),
0
),
// When calculate is not selected
SWITCH(
GoalDir,
1, IF(TargetMeasure <> 1, (1 - ActualMeasure) / (1 - TargetMeasure), 0),
-1, IF(TargetMeasure <> 1, 2 - ((1 - ActualMeasure) / (1 - TargetMeasure)), 0),
0, IF(TargetMeasure <> 0, 1 - ABS(ActualMeasure - TargetMeasure) / ABS(TargetMeasure), 0)
)
),
"divide",
IF(
TargetMeasure <> 0,
IF(
IsCalculateSelected,
(((ActualMeasure / TargetMeasure) - [target 2]) * GoalDir / [target 2]) + 1,
((ActualMeasure - TargetMeasure) * GoalDir / TargetMeasure) + 1
),
0
),
"Doubled",
IF(
IsCalculateSelected,
((ActualMeasure - TargetMeasure) * GoalDir / TargetMeasure) + 1,
BLANK()
),
BLANK() // Default case for unknown calculation types
)
)

// Ensure result is between 0 and 1.5
RETURN
IF(
ISBLANK(ResultValue),
BLANK(),
MAX(0, MIN(ResultValue, 1.5))
)
)

// Return the average of all pre-aggregated KPI results
RETURN
AVERAGEX(
PreAggregateResults,
[SingleKPIResult]
)

  • aabdal03 , Try using

    dax
    KPI Achievement Per Month =
    VAR PreAggregateResults =
    SUMMARIZE(
    VALUES('01.00sc supplychain'[KPI]),
    '01.00sc supplychain'[KPI],
    "SingleKPIResult",
    VAR GoalDir = MAX('01.00sc supplychain'[Goal direction])
    VAR IsCalculateSelected =
    CONTAINS(
    ALLSELECTED('02.sc slicer'),
    '02.sc slicer'[Slicersc],
    "07.calculate"
    )
    VAR ActualMeasure = IF(IsCalculateSelected, [calc actual2], [actual 2])
    VAR TargetMeasure = IF(IsCalculateSelected, [Calc Target 2], [target 2])
    VAR CalcType = FIRSTNONBLANK('01.00sc supplychain'[calculation f], 1)
    VAR IsActualZero = ActualMeasure = 0
    VAR IsTargetZero = TargetMeasure = 0

    // Edge case handling for zero values
    VAR EdgeCaseResult =
    IF(
    AND(IsActualZero, IsTargetZero),
    1,
    BLANK()
    )
    // Main calculation logic
    VAR ResultValue =
    IF(
    NOT ISBLANK(EdgeCaseResult),
    EdgeCaseResult,
    SWITCH(
    CalcType,
    "deviation",
    IF(
    IsCalculateSelected,
    IF(
    TargetMeasure <> 0,
    ((ActualMeasure - TargetMeasure) / TargetMeasure) * 100,
    0
    ),
    // When calculate is not selected
    SWITCH(
    GoalDir,
    1, IF(TargetMeasure <> 0, ((ActualMeasure - TargetMeasure) / TargetMeasure) * 100, 0),
    -1, IF(TargetMeasure <> 0, ((TargetMeasure - ActualMeasure) / TargetMeasure) * 100, 0),
    0, IF(TargetMeasure <> 0, (ABS(ActualMeasure - TargetMeasure) / ABS(TargetMeasure)) * 100, 0)
    )
    ),
    "divide",
    IF(
    TargetMeasure <> 0,
    IF(
    IsCalculateSelected,
    (((ActualMeasure / TargetMeasure) - [target 2]) * GoalDir / [target 2]) + 1,
    ((ActualMeasure - TargetMeasure) * GoalDir / TargetMeasure) + 1
    ),
    0
    ),
    "Doubled",
    IF(
    IsCalculateSelected,
    ((ActualMeasure - TargetMeasure) * GoalDir / TargetMeasure) + 1,
    BLANK()
    ),
    BLANK() // Default case for unknown calculation types
    )
    )

    // Ensure result is between 0 and 1.5
    RETURN
    IF(
    ISBLANK(ResultValue),
    BLANK(),
    MAX(0, MIN(ResultValue, 1.5))
    )
    )

    // Return the average of all pre-aggregated KPI results
    RETURN
    AVERAGEX(
    PreAggregateResults,
    [SingleKPIResult]
    )

1 Reply

  • aabdal03 , Try using

    dax
    KPI Achievement Per Month =
    VAR PreAggregateResults =
    SUMMARIZE(
    VALUES('01.00sc supplychain'[KPI]),
    '01.00sc supplychain'[KPI],
    "SingleKPIResult",
    VAR GoalDir = MAX('01.00sc supplychain'[Goal direction])
    VAR IsCalculateSelected =
    CONTAINS(
    ALLSELECTED('02.sc slicer'),
    '02.sc slicer'[Slicersc],
    "07.calculate"
    )
    VAR ActualMeasure = IF(IsCalculateSelected, [calc actual2], [actual 2])
    VAR TargetMeasure = IF(IsCalculateSelected, [Calc Target 2], [target 2])
    VAR CalcType = FIRSTNONBLANK('01.00sc supplychain'[calculation f], 1)
    VAR IsActualZero = ActualMeasure = 0
    VAR IsTargetZero = TargetMeasure = 0

    // Edge case handling for zero values
    VAR EdgeCaseResult =
    IF(
    AND(IsActualZero, IsTargetZero),
    1,
    BLANK()
    )
    // Main calculation logic
    VAR ResultValue =
    IF(
    NOT ISBLANK(EdgeCaseResult),
    EdgeCaseResult,
    SWITCH(
    CalcType,
    "deviation",
    IF(
    IsCalculateSelected,
    IF(
    TargetMeasure <> 0,
    ((ActualMeasure - TargetMeasure) / TargetMeasure) * 100,
    0
    ),
    // When calculate is not selected
    SWITCH(
    GoalDir,
    1, IF(TargetMeasure <> 0, ((ActualMeasure - TargetMeasure) / TargetMeasure) * 100, 0),
    -1, IF(TargetMeasure <> 0, ((TargetMeasure - ActualMeasure) / TargetMeasure) * 100, 0),
    0, IF(TargetMeasure <> 0, (ABS(ActualMeasure - TargetMeasure) / ABS(TargetMeasure)) * 100, 0)
    )
    ),
    "divide",
    IF(
    TargetMeasure <> 0,
    IF(
    IsCalculateSelected,
    (((ActualMeasure / TargetMeasure) - [target 2]) * GoalDir / [target 2]) + 1,
    ((ActualMeasure - TargetMeasure) * GoalDir / TargetMeasure) + 1
    ),
    0
    ),
    "Doubled",
    IF(
    IsCalculateSelected,
    ((ActualMeasure - TargetMeasure) * GoalDir / TargetMeasure) + 1,
    BLANK()
    ),
    BLANK() // Default case for unknown calculation types
    )
    )

    // Ensure result is between 0 and 1.5
    RETURN
    IF(
    ISBLANK(ResultValue),
    BLANK(),
    MAX(0, MIN(ResultValue, 1.5))
    )
    )

    // Return the average of all pre-aggregated KPI results
    RETURN
    AVERAGEX(
    PreAggregateResults,
    [SingleKPIResult]
    )