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

     

manojk_pbi's avatar
manojk_pbi
Helper V
2 years ago

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%

 

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.