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

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

     

9 Replies

    • 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