cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper III

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 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%.

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

1 ACCEPTED SOLUTION
Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
9 REPLIES 9
Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper III

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%

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper III

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
Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper III

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.

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper III

Thanks @Greg_Deckler  for this solution.

Helper III

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.

Announcements

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors