Forum Discussion

manojk_pbi's avatar
manojk_pbi
Helper V
2 years ago
Solved

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 simp...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    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

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
2 years ago

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's avatar
manojk_pbi
Helper V
2 years ago

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
  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    2 years ago

    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's avatar
      manojk_pbi
      Helper V
      2 years ago

      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's avatar
        Greg_Deckler
        Community Champion
        2 years ago

        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