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

        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.