Forum Discussion
manojk_pbi
2 years agoHelper V
Need help in DAX formula
Hi Friend, I have one requirement where in we have some deliver percentage values. I need to recalculate these values using the targets defined to derive normalized values. Providing below a simp...
- 2 years ago
manojk_pbi JC, glad you explained it because I would have never figured that out from the information provided. Do this (PBIX is attached)
Measure = VAR __Value = [December] VAR __ThreshMin = MAXX(FILTER('Thresholds', [Value] <= __Value), [Attribute]) VAR __ThreshMinVal = MAXX(FILTER('Thresholds', [Value] <= __Value), [Value]) VAR __ThreshMax = MAXX(FILTER('Thresholds', [Value] > __Value), [Attribute]) VAR __ThreshMaxVal = MAXX(FILTER('Thresholds', [Value] > __Value), [Value]) VAR __Result = SWITCH( TRUE(), __ThreshMax = BLANK(), __Value, __Value = __ThreshMax, __Value, DIVIDE( __ThreshMax - __ThreshMin, __ThreshMaxVal - __ThreshMinVal) * (__Value - __ThreshMinVal ) + __ThreshMin ) RETURN __Result
manojk_pbi
2 years agoHelper V
Thanks Greg_Deckler for this solution.
manojk_pbi
2 years agoHelper V
Hi Greg_Deckler ,
I noticed something strange when the value is more than threshold value eg : in the sample we have taken 93% is equal to 150% but if value is 94 then it should return 150% rather it is returning 9400%. How this can be tweaked ? Same will be applied to lower limit as well.