Forum Discussion
Need help in DAX formula
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 So why exactly does 86% become 106%? How does that work exactly?
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%
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 __ResultThanks 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 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.