Forum Discussion

CatManKuhn's avatar
CatManKuhn
Icon for Helper II rankHelper II
5 years ago
Solved

Dynamic Percentile and Sum Above 80th Percentile

Hi all,    I have a doozy of a question. I am working on calculating a sum of all workers who are above the 80th percentile of tenure. I have a measure created that identifies the 80th percentile a...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    5 years ago

    Hi, CatManKuhn ;

    Please try it.

    flag = 
var _a=CALCULATE(PERCENTILE.INC([Tenure],0.8),ALLEXCEPT(Terminations,Terminations[Office Name]))
VAR _b =
    CALCULATE (
        PERCENTILE.INC ( Terminations[Tenure], .80 ),
        ALLEXCEPT ( Terminations, 'Terminations'[depart] ))
VAR _c =
    CALCULATE (
        PERCENTILE.INC ( Terminations[Tenure], .80 ),
        ALLEXCEPT ( Terminations, Terminations[depart2] ))

VAR Cutoff = [80th Percentile]
var _80= CALCULATE ( COUNTROWS ( Terminations ), Terminations[Tenure] >= Cutoff )

RETURN
  IF(ISINSCOPE(Terminations[depart]),IF(_a<=_b,_80),
  IF(ISINSCOPE(Terminations[depart2]),IF(_b<=_c,_80),_80))

    The final output is shown below:

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-yalanwu-msft's avatar
v-yalanwu-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi, CatManKuhn ;

You could modify the measure as follows:

flag = 
VAR _a =
    CALCULATE (
        PERCENTILE.INC ( Terminations[Tenure], .80 ),
        ALLEXCEPT ( Terminations, 'Terminations'[depart] ))
VAR _b =
    CALCULATE (
        PERCENTILE.INC ( Terminations[Tenure], .80 ),
        ALLEXCEPT ( Terminations, Terminations[depart2] ))
VAR Cutoff = [80th Percentile]
var _80= CALCULATE ( COUNTROWS ( Terminations ), Terminations[Tenure] >= Cutoff )

RETURN
  IF(ISINSCOPE(Terminations[depart2]),IF(_a<=_b,_80),_80)

Then change the visual filter is "is not blank" .

In addition, you should change the 80th Percentile measure.

80th Percentile = 
IF (
    ISINSCOPE ( Terminations[depart] ),
    CALCULATE (PERCENTILE.INC ( Terminations[Tenure], .80 ),ALLEXCEPT ( Terminations, Terminations[depart] )),

    IF (
        ISINSCOPE ( Terminations[depart2] ),
        CALCULATE (PERCENTILE.INC ( [Tenure], 0.8 ),ALLEXCEPT ( Terminations, Terminations[depart2] )),
        
        CALCULATE (PERCENTILE.INC ( [Tenure], 0.8 ),ALLEXCEPT ( Terminations, Terminations[Office Name] ))))

The final output is shown below:

 

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • CatManKuhn's avatar
    CatManKuhn
    Icon for Helper II rankHelper II
    5 years ago

    Hi v-yalanwu-msft 

     

    I took a look at the file you provided. It is really close, but there are a few rows that I would not expect to see. 

    I wouldn't expect to see row "aa" -> "aa" because their tenure is 1.0 which is less than the 80th percentile of the parent row ("A") which the 80th percentile is 1.80. Similarily, I wouldn't expect to see the Data Analyst role since their tenure is 3.50 which is less than the 80th percentile of Texas (3.76).

     

    Please let me know what other information I can provide to assist. I am completely stumped and I am unsure if this is possible. I may need to discuss with the requestor to redefine the ask.

     

    Thanks for the help!

     

    • v-yalanwu-msft's avatar
      v-yalanwu-msft
      Icon for Community Support rankCommunity Support
      5 years ago

      Hi, CatManKuhn ;

      Please try it.

      flag = 
var _a=CALCULATE(PERCENTILE.INC([Tenure],0.8),ALLEXCEPT(Terminations,Terminations[Office Name]))
VAR _b =
    CALCULATE (
        PERCENTILE.INC ( Terminations[Tenure], .80 ),
        ALLEXCEPT ( Terminations, 'Terminations'[depart] ))
VAR _c =
    CALCULATE (
        PERCENTILE.INC ( Terminations[Tenure], .80 ),
        ALLEXCEPT ( Terminations, Terminations[depart2] ))

VAR Cutoff = [80th Percentile]
var _80= CALCULATE ( COUNTROWS ( Terminations ), Terminations[Tenure] >= Cutoff )

RETURN
  IF(ISINSCOPE(Terminations[depart]),IF(_a<=_b,_80),
  IF(ISINSCOPE(Terminations[depart2]),IF(_b<=_c,_80),_80))

      The final output is shown below:

      Best Regards,
      Community Support Team_ Yalan Wu
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.