topic Re: Third quartile function does not give the correct value in DAX Commands and Tips

Third quartile function does not give the correct value

Anonymous — Tue, 01 Mar 2022 12:54:19 GMT

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.

Re: Third quartile function does not give the correct value

Whitewater100 — Tue, 01 Mar 2022 14:02:42 GMT

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!

)

Re: Third quartile function does not give the correct value

Anonymous — Tue, 01 Mar 2022 14:16:02 GMT

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.

Re: Third quartile function does not give the correct value

Anonymous — Fri, 04 Mar 2022 07:47:32 GMT

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.

Re: Third quartile function does not give the correct value

Whitewater100 — Fri, 04 Mar 2022 12:27:08 GMT

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

)

Re: Third quartile function does not give the correct value

SalmanABDul_97 — Sat, 05 Mar 2022 14:20:51 GMT

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

Re: Third quartile function does not give the correct value

Whitewater100 — Sun, 06 Mar 2022 20:03:07 GMT

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!

Re: Third quartile function does not give the correct value

Anonymous — Mon, 07 Mar 2022 07:45:12 GMT

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.