Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Third quartile function does not give the correct value

Hi everyon

 

i have been trying to solve this problem but I could not, i hope someone has faced the same problem.

 

I have multiple Tables, the important ones are:

KPI_fact

Hospitals 

 

KPI_fact has tow importent columns:

 [Numerator], [denominator] and Provider ID , ID of diagnosis. 

I create new measure for

ActualValue = 

var a = SUM('KPI_fact '[Numerator])
var b = SUM('KPI_fact '[Denominator] )
return DIVIDE(a,b)

 

Then I create another measure to Qaluclate the third-Qurtile for each Hospital after Suming:

 

Q3 = (PERCENTILEX.INC(
ALL ('KPI_fact'[IDofdiagnosis]),
[ActualValue ],
0.75
))
 
____
 
This is the Table with ActualValue and Hospital IDs , I added filter for one ID of diagnosis in the visual.

and this is the table after adding the Third Qurtile measure 

 

 

What i need is Just one Value for ALL the KPIValue values in the table, thirdQurtile should be 1, I tested it in EXCEL 

I need the measure to Consider just the KPIvalue that disply in the table to do Calculation on. not else! 

 

if there is anything unclear , please ask. 

 

 

 

 

 

 

 

 

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

    Please refer to my pbix file to see if it helps you.

    Create a measure. 

    Measure = CALCULATE(PERCENTILEX.INC(Sheet1,[Actvalue],.75),FILTER(ALL(Sheet1),Sheet1[ICD10_PrincipalDiagnosisKey]=SELECTEDVALUE(Sheet1[ICD10_PrincipalDiagnosisKey])))

    If I have miunderstood your meaning, please provide your desired output.

     

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

7 Replies

  • Whitewater100's avatar
    Whitewater100
    Solution Sage
    4 years ago

    Hi:

    3rd Quartile KPI =

    VAR ThirdQuartile = [Q3]
    RETURN
    IF ([Actual Value] = ThirdQuartile, "Q3",
    "-")
    You can substitute "1" where I have "Q3"
     
    I hope this is what you are looking for!
    )
    )
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      thank you for your response. 

      the problem is in the third qurtile value. the value is not correct. i think the measure read another data not same data in the table. try to calculate the values in the table manully and find the thirdQ. you will find it wrong.

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

    Since your data is rather vague, I recreated a simple sample. Please refer to my pbix file to see if it helps you.

    My measure formula is 

    Measure =
VAR _a =
    SUM ( 'Table (2)'[Numerator] )
VAR _b =
    SUM ( 'Table (2)'[denominator] )
RETURN
    DIVIDE ( _a, _b )

    this is the table after adding the Third Qurtile measure.

    Measure2= PERCENTILEX.INC(ALL('Table (2)'),[Measure],.75)

    If I have misunderstood your meaning, please provide your pbix file without privacy information and desired output.

     

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • SalmanABDul_97's avatar
      SalmanABDul_97
      Helper I
      4 years ago

      Can't thank you enough. 

       

      i provided pbix file so you can understand my problem. 

      when i change dignosis id its give me the same result of Q3. 

      i want it to consider the filter 

      it should see the filter then do the Qalqulation.

       

      I want the process to be as follows:

      the filter is applied and then the third ŁŽQurtile of the data that appeared after the filter is calculated. The Q-value must be one Q-value for all filtered data. In short, I want to see the third Qurtile for each diagnosis-id I choose. 

       

      here is the file 

      ThirdQurtilefile

  • Whitewater100's avatar
    Whitewater100
    Solution Sage
    4 years ago

    Hello:

    If you share your data model it will be easier to answer.

     

    If you create this for each quartile:

    Q3 = (PERCENTILEX.INC(
    ALL ('KPI_fact'[IDofdiagnosis]),
    [ActualValue ],
    0.75                                 other versions  >>>.25, .5 
    ))
     
    You can aslo try this version, applied to yuor data :
    Customer Sales 25th Percentile 2 =
    PERCENTILEX.INC (
    FILTER(
    Customers,
    NOT ISBLANK([Sales Amount] )
    ),
    [Sales Amount],
    .25
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

    Please refer to my pbix file to see if it helps you.

    Create a measure. 

    Measure = CALCULATE(PERCENTILEX.INC(Sheet1,[Actvalue],.75),FILTER(ALL(Sheet1),Sheet1[ICD10_PrincipalDiagnosisKey]=SELECTEDVALUE(Sheet1[ICD10_PrincipalDiagnosisKey])))

    If I have miunderstood your meaning, please provide your desired output.

     

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.