dax with chARTICULATOR VISULA

aabdal03 — Mon, 19 May 2025 08:47:16 GMT

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

Re: dax with chARTICULATOR VISULA

bhanu_gautam — Mon, 19 May 2025 09:25:02 GMT

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

topic dax with chARTICULATOR VISULA in DAX Commands and Tips

dax with chARTICULATOR VISULA

Re: dax with chARTICULATOR VISULA