Need help in DAX formula

[manojk_pbi]

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 simple example with data. I am not expert in DAX nor in Modeling. Please suggest how this can be achieved.

My actual value for the Dec'23 is 86%, have to derive the normalized value using the below Threshold table.

Calculation would be something like below, expected result for 86% is 106%

for 85% it is 100% as per threshold, we need calculate what will the value of 1%.

2023 Threshold	(0%)	(50%)	(100%)	(150%)
Delivery	72	78	85	93

Thanks in advance,

[Greg_Deckler]

@manojk_pbi So why exactly does 86% become 106%? How does that work exactly?

[manojk_pbi]

The calculation goes like below,

86 is greater than 85 & less than 93, so 86 is one percent more than the threshold value so we need to calculate the converstion of 1% into the threshold.

ie. 93-85 = 8

150-100=50

Per 1% = (50/8) which is 6.25

there fore, 86% is equal to 106.2%

[Greg_Deckler · Accepted solution]

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

Thanks @Greg_Deckler  for your quick solution. This will help.

Can you suggest how the same can be extended when we have values in table like below across different years. 

	Year	0	50	100	150
Threshold	2023	72	78	85	93
Threshold	2024	72	75	80	90

MonthYear	Delivery%
Dec-23	86
Nov-23	84
Oct-23	83

[Greg_Deckler]

@manojk_pbi I would still unpivot the last four columns of that table. Probably create a Year column in your Delivery table and relate that to your thresholds which should also have a straight Year column. After that, things should be very straight-forward.

[manojk_pbi]

Hi @Greg_Deckler , I am new to DAX queries and i am not sure how the table links works. Please could you create a sample for me if it doen't take much time. This will be of great help.

Please suggest me some materials for better understanding of DAX queries.

[Greg_Deckler]

@manojk_pbi Updated. See attached PBIX.

Measure = 
    VAR __Value = MAX('Delivery'[Delivery%])
    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]

Thanks @Greg_Deckler  for this solution. 

[manojk_pbi]

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.