Skip to main content
cancel
Showing results for 
Search instead 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

Reply
manojk_pbi
Helper III
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%.

 

2023

Threshold

(0%) (50%)(100%)(150%)
Delivery72788593

 

Thanks in advance,

1 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

 


Follow on LinkedIn
@ 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...

View solution in original post

9 REPLIES 9
Greg_Deckler
Super User
Super User

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


Follow on LinkedIn
@ 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...

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
    __Result

 


Follow on LinkedIn
@ 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...

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. 

 

 Year050100150
Threshold202372788593
Threshold202472758090

 

MonthYearDelivery%
Dec-2386
Nov-2384
Oct-2383

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


Follow on LinkedIn
@ 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...

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.

@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

Follow on LinkedIn
@ 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...

Thanks @Greg_Deckler  for this solution. 

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.

manojk_pbi_0-1711411669542.png

 

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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.