Third quartile function does not give the correct value

[Anonymous]

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 · Accepted solution]

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.

[Whitewater100]

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]

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]

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]

Hi:

I have this so far.

https://drive.google.com/file/d/1JGQKQxFTFkllOCFciQNeFE7xNljaUd14/view?usp=sharing 

But the third quartile is against all the values. Is that what you are looking for or for each diagnosis the third quartile? Many diagnosis look to have low entries. I hope this helps!

[Whitewater100]

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]

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.