cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
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?

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
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``````

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
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.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
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``````

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors